:::: MENU ::::

Implementing retention span for SQL Server backup device

  • Nov 17 / 2012
  • 0
Backup and Restore, dbDigger, Disaster Recovery, SQL Server Training

Implementing retention span for SQL Server backup device

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

At this point we require some way to ensure that backups on the device would not be overwritten even by accidentally using the WITH INIT option. There may be two approaches to make sure the retention of backups on the device for a specified time.
  • 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.

Note:

Although WITH INIT would not be able to overwrite the retained media but WITH FORMAT may overwrite the header and content of device.

Consult us to explore the Databases. Contact us