Sunday, March 11, 2012

Differential Backup Files

When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.pretty easy to do via T-SQL statement. The following script creates a backup with date and hour as timestamp in file name.

declare @.hour varchar(2), @.date varchar(8)
set @.hour = substring(convert(char(2), getdate(), 108), 1, 2)
set @.date = convert(varchar(8), getdate(), 112)
exec ('use master backup database xxx to disk = ''D:\backup\xxx_db_' + @.date + @.hour + '.bak '' with init')

No comments:

Post a Comment