SQL Server uses logical devices or media to perform database backups. Backups may be append or overwrite to existing backups in these media sets or devices. It may be a serious issue when a backup in device is silently overwritten without your notice. This scenario may occur if you repeat a previous backup device name with INIT option. Consider the following example for this scenario
-- Create a backup BACKUP DATABASE DBDIGGER TO DISK = 'E:DBD-BackupDemo.bak' GO /* Create another backup with same device name. By default backup would be performed by using WITH NOINIT option and would be appended */ BACKUP DATABASE DBDIGGER TO DISK = 'E:DBD-BackupDemo.bak' GO -- Verify the two backups on backup device RESTORE HEADERONLY FROM DISK = 'E:DBD-BackupDemo.bak' GO
Here we are shown two backups on the device. It shows that second backup was also appended to the device. Going further we would issue another backup command by using WITH INIT. This time backup would overwrite the existing backups on the device.
/* Create another backup with same device name and INIT option. It would overwrite the existing backups on device. */ BACKUP DATABASE DBDIGGER TO DISK = 'E:DBD-BackupDemo.bak' WITH INIT GO
Now verify that only last backup exists on the device and all previous backups were overwritten.
/* Verify that previous backups are overwritten leaving behind the last backup */ RESTORE HEADERONLY FROM DISK = 'E:DBD-BackupDemo.bak' GO
RETAINING THE BACKUPS ON THE DEVICE
- Use WITH EXPIREDATE or WITH RETAINDAYS
- Set retention days server wide for all databases
Following is a brief description of these two options
WITH EXPIREDATE | WITH RETAINDAYS days
This option operates along with backup command for a specific device of a database. We may specify a date or days along with WITH EXPIREDATE or WITH RETAINDAYS option respectively. Here we would create a backup device with retention span of two days
-- Create a backup with retention span 2 days BACKUP DATABASE DBDIGGER TO DISK = 'E:DBD-BackupDemo2.bak' WITH RETAINDAYS = 2 GO
Now create a backup in same device along with using WITH INIT option. This time error would be generated because backups in the device are retained for two days.
/* Create another backup with same device name and INIT option. It would overwrite the existing backups on device. */ BACKUP DATABASE DBDIGGER TO DISK = 'E:DBD-BackupDemo2.bak' WITH INIT GO
If next backups are created in the device without any retention parameter even then device would maintain the backups with retention parameter and could not be overwritten for next two days. We may get the expiration date or retention span of files by using RESTORE HEADERONLY.
Similarly EXPIREDATE option may be used to specify an expiration date to which backups on the device would be retained. Date provided should be according to system configured date format.
SET RETENTION DAYS AT SERVER LEVEL
We have another option to include the fixed retention days for every backup device of databases on that very server. For using this method
- Right click on instance
- Go to properties
- Go to database settings in the left panel
- Set default media retention span in days here. By default it is zero
The retention days span here would automatically be implemented for every backup device of that server without explicit use of EXPIREDATE or RETAINDAYS.
Although WITH INIT would not be able to overwrite the retained media but WITH FORMAT may overwrite the header and content of device.