Automating differential backups
-
Is there a way to automate differential backups the same way that full/tran backups are in the Maint. Plans? Or do you have to just schedule a backup command as a job?
-Dan
-Dan -
-
This is the TSQL used in my job to perform the Differential backup of all my databases to tape except distribution, master, model and tempdb.
ALso I change my tape out on Friday so I have the tape automatically unload for me to save time.
DECLARE @dbname sysname
DECLARE @sql varchar(2000)
SET @dbname = (SELECT TOP 1 [name] FROM sysdatabases WHERE [name] != 'tempDb' ORDER BY [name] DESC)
SET @sql = 'IF DatePart(w,GetDate()) = 6 AND ''?'' = (''' + @dbname + ''')
begin
BACKUP DATABASE
TO TAPE = ''\\.\Tape0''
WITH
DIFFERENTIAL,
UNLOAD,
NAME = ''? Full Backup ' + RTRIM(CONVERT(CHAR,GETDATE(),101)) + '''
end
else
begin
if ''?'' NOT IN (''distribution'',''master'',''model'',''tempdb'')
begin
BACKUP DATABASE
TO TAPE = ''\\.\Tape0''
WITH
DIFFERENTIAL,
NOUNLOAD,
NAME = ''? Full Backup ' + RTRIM(CONVERT(CHAR,GETDATE(),101)) + '''
end
end
'
EXEC sp_MSForEachDB @sql -
Thanks for the script...maybe in the next version of SQL Server it will be just another tab in the Maint. Plan...
-Dan
-Dan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply