Hi,
Running SQL 7. We have a maintanence plan doing transaction log backups
every hour. Outside of the maintanence plan we have a job that does a
differential backup of the database. My question is, when the differential
backup occurs, is the transaction log truncated? For example if the last
transaction log backup was at 1pm, and a differential at 1:30pm, then
another transaction log backup at 2pm; will the 2pm transaction log backup
contain the transactions at 1:15pm?
Secondly, how can I modify the differential backup job below to:
a) name the filename of the diff backup file to be
<database_name>_diff_MMDDYYhhmmss.bak
b) have the script remove diff backup files older than 1 day.oops! here is the script I need to modify that I referred to below:
BACKUP DATABASE [myDB] TO DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH INIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'myDB latest diff backup', SKIP , STATS = 10,
DESCRIPTION = N'every 5 hour diff', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='myDB'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='myDB')
RESTORE VERIFYONLY FROM DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH FILE = @.i
"aaz" <aaz@.webcapacity.com> wrote in message
news:uSP7mY$SDHA.3188@.tk2msftngp13.phx.gbl...
> Hi,
> Running SQL 7. We have a maintanence plan doing transaction log backups
> every hour. Outside of the maintanence plan we have a job that does a
> differential backup of the database. My question is, when the differential
> backup occurs, is the transaction log truncated? For example if the last
> transaction log backup was at 1pm, and a differential at 1:30pm, then
> another transaction log backup at 2pm; will the 2pm transaction log backup
> contain the transactions at 1:15pm?
> Secondly, how can I modify the differential backup job below to:
> a) name the filename of the diff backup file to be
> <database_name>_diff_MMDDYYhhmmss.bak
> b) have the script remove diff backup files older than 1 day.
>|||Azz
Running a differential backup does not truncate the
transaction log, it just records all the changes to the
database sine the last full backup.
Bear in mind you should not just do differential backups,
you should do a full backup as well as part of you backup
strategy. How often depends on the size of your database
and how dynamic the data is.
If you do not do full backups eventually your differential
will be as large and take as long as a full backup, and
you will still need a full backup to use it.
Regards
John|||yeah we are also doing fulls once a day, diffs 2x, then transaction logs
every hour. I just wanted to make sure that running the differential did not
truncate the transaction logs.
can anyone help with the second 1/2 of my question?
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> Azz
> Running a differential backup does not truncate the
> transaction log, it just records all the changes to the
> database sine the last full backup.
> Bear in mind you should not just do differential backups,
> you should do a full backup as well as part of you backup
> strategy. How often depends on the size of your database
> and how dynamic the data is.
> If you do not do full backups eventually your differential
> will be as large and take as long as a full backup, and
> you will still need a full backup to use it.
> Regards
> John|||Here is a sample to start from:
-- Separate file for each day of the week --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\MVP\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD D:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "D:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly
SQL Server MVP
"aaz" <aaz@.webcapacity.com> wrote in message
news:Owh8jYHTDHA.2460@.TK2MSFTNGP10.phx.gbl...
> yeah we are also doing fulls once a day, diffs 2x, then transaction logs
> every hour. I just wanted to make sure that running the differential did
not
> truncate the transaction logs.
> can anyone help with the second 1/2 of my question?
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> > Azz
> >
> > Running a differential backup does not truncate the
> > transaction log, it just records all the changes to the
> > database sine the last full backup.
> >
> > Bear in mind you should not just do differential backups,
> > you should do a full backup as well as part of you backup
> > strategy. How often depends on the size of your database
> > and how dynamic the data is.
> >
> > If you do not do full backups eventually your differential
> > will be as large and take as long as a full backup, and
> > you will still need a full backup to use it.
> >
> > Regards
> >
> > John
>
guyguy2003 wrote:
Phil Brammer wrote: