Backup schedule (Differential backups)

Hello all, hope someone will be able to help.
I have a number of medium-sized systems that have the following backup regime:
1. Daily full backups
2. Hourly transaction log backups
3. During the window 9p.m â' 6.a.m the system drive is backed up by TSM.
The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
The old backup files deleted after 48hours as part of the plan to free up
disk space.
A couple of the database grew just over 12GB and I would like implement the
following back schedule for them:
Mon - Differential database backup
Tue - Differential database backup
Wed - Differential database backup
Thu - Differential database backup
Fri - Differential database backup
Sat - Differential database backup
Sun Full database backup
All days Transaction log backups as above
I have scheduled two jobs, one for weekly full backups and the other for
daily differential backups. My question is â'How do I rollover to the next
week?â' I need somehow override the backup device used for differential
backups at the end of the week. I would like to keep all differential
backups for the week and use append to media option. I could not figure out
how to create a separate file as sqlmaint does for complete backups.
Regards,
ruskiIt's pretty easy to create a new file name for each backup.
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
SET @.DBName = 'YourDBName'
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
SET @.Name = @.DBName + N' Differential Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
But if you want to use the same filename and overwrite it once a week you
can do something like this:
DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
SET @.DBName = 'YourDBName'
SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
SET @.Name = @.DBName + N' Differential Backup'
IF DATEPART(dw,GETDATE()) = 1
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
ELSE
BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT , DIFFERENTIAL,
NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
Andrew J. Kelly SQL MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
> Hello all, hope someone will be able to help.
> I have a number of medium-sized systems that have the following backup
> regime:
> 1. Daily full backups
> 2. Hourly transaction log backups
> 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
> The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
> The old backup files deleted after 48hours as part of the plan to free up
> disk space.
> A couple of the database grew just over 12GB and I would like implement
> the
> following back schedule for them:
> Mon - Differential database backup
> Tue - Differential database backup
> Wed - Differential database backup
> Thu - Differential database backup
> Fri - Differential database backup
> Sat - Differential database backup
> Sun Full database backup
> All days Transaction log backups as above
> I have scheduled two jobs, one for weekly full backups and the other for
> daily differential backups. My question is "How do I rollover to the next
> week?" I need somehow override the backup device used for differential
> backups at the end of the week. I would like to keep all differential
> backups for the week and use append to media option. I could not figure
> out
> how to create a separate file as sqlmaint does for complete backups.
> Regards,
> ruski
>|||Thanks, Andrew. That's exactly what I was hopping to achieve. And if I go
with the first solution, would you use xp_cmdshell for deletion of the all
old backup files at the end of the week?
Regards,
ruski
"Andrew J. Kelly" wrote:
> It's pretty easy to create a new file name for each backup.
>
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> SET @.DBName = 'YourDBName'
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
> SET @.Name = @.DBName + N' Differential Backup'
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
>
> But if you want to use the same filename and overwrite it once a week you
> can do something like this:
> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
> SET @.DBName = 'YourDBName'
> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
> SET @.Name = @.DBName + N' Differential Backup'
> IF DATEPART(dw,GETDATE()) = 1
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
> ELSE
> BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT , DIFFERENTIAL,
> NOUNLOAD ,
> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>
> --
> Andrew J. Kelly SQL MVP
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
> > Hello all, hope someone will be able to help.
> >
> > I have a number of medium-sized systems that have the following backup
> > regime:
> > 1. Daily full backups
> > 2. Hourly transaction log backups
> > 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
> > The plan is created and maintained by the SQL Maintenance plan (sqlmaint).
> > The old backup files deleted after 48hours as part of the plan to free up
> > disk space.
> >
> > A couple of the database grew just over 12GB and I would like implement
> > the
> > following back schedule for them:
> >
> > Mon - Differential database backup
> > Tue - Differential database backup
> > Wed - Differential database backup
> > Thu - Differential database backup
> > Fri - Differential database backup
> > Sat - Differential database backup
> > Sun Full database backup
> > All days Transaction log backups as above
> >
> > I have scheduled two jobs, one for weekly full backups and the other for
> > daily differential backups. My question is "How do I rollover to the next
> > week?" I need somehow override the backup device used for differential
> > backups at the end of the week. I would like to keep all differential
> > backups for the week and use append to media option. I could not figure
> > out
> > how to create a separate file as sqlmaint does for complete backups.
> >
> > Regards,
> > ruski
> >
>
>|||That is one way to do it. See the attached example. But you can also use
other methods such as the File Scripting Object (FSO) in an Active-X job as
well.
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 C:\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 "C:\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 MVP
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:E480C761-8130-4A7B-B7EB-B032B0440C1E@.microsoft.com...
> Thanks, Andrew. That's exactly what I was hopping to achieve. And if I go
> with the first solution, would you use xp_cmdshell for deletion of the all
> old backup files at the end of the week?
> Regards,
> ruski
> "Andrew J. Kelly" wrote:
>> It's pretty easy to create a new file name for each backup.
>>
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>> SET @.DBName = 'YourDBName'
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff_' +
>> CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'
>> SET @.Name = @.DBName + N' Differential Backup'
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>>
>> But if you want to use the same filename and overwrite it once a week you
>> can do something like this:
>> DECLARE @.DBName NVARCHAR(100), @.Device NVARCHAR(100), @.Name NVARCHAR(150)
>> SET @.DBName = 'YourDBName'
>> SET @.Device = N'C:\Data\Backups\DD_' + @.DBName + '_Diff.BAK'
>> SET @.Name = @.DBName + N' Differential Backup'
>> IF DATEPART(dw,GETDATE()) = 1
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>> ELSE
>> BACKUP DATABASE @.DBName TO DISK = @.Device WITH NOINIT ,
>> DIFFERENTIAL,
>> NOUNLOAD ,
>> NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:420D0400-5FD9-461C-B164-2CE18E17444B@.microsoft.com...
>> > Hello all, hope someone will be able to help.
>> >
>> > I have a number of medium-sized systems that have the following backup
>> > regime:
>> > 1. Daily full backups
>> > 2. Hourly transaction log backups
>> > 3. During the window 9p.m - 6.a.m the system drive is backed up by TSM.
>> > The plan is created and maintained by the SQL Maintenance plan
>> > (sqlmaint).
>> > The old backup files deleted after 48hours as part of the plan to free
>> > up
>> > disk space.
>> >
>> > A couple of the database grew just over 12GB and I would like implement
>> > the
>> > following back schedule for them:
>> >
>> > Mon - Differential database backup
>> > Tue - Differential database backup
>> > Wed - Differential database backup
>> > Thu - Differential database backup
>> > Fri - Differential database backup
>> > Sat - Differential database backup
>> > Sun Full database backup
>> > All days Transaction log backups as above
>> >
>> > I have scheduled two jobs, one for weekly full backups and the other
>> > for
>> > daily differential backups. My question is "How do I rollover to the
>> > next
>> > week?" I need somehow override the backup device used for differential
>> > backups at the end of the week. I would like to keep all differential
>> > backups for the week and use append to media option. I could not
>> > figure
>> > out
>> > how to create a separate file as sqlmaint does for complete backups.
>> >
>> > Regards,
>> > ruski
>> >
>>