As you might know already, the expiration date on a backup is the date (or number of days since backup) when the file will no longer be seen as useful, and should generally be discarded or overwritten.
Before we get to the two methods of setting this parameter on backups, you should know that you need permissions to BACKUP DATABASE and BACKUP LOG commands before you can set the expiration date, permissions which are already present for the members of sysadmin, db_owner and db_backupoperator fixed server roles.
Set expiration date on a backup using T-SQL
The following query is the one you should use set the expiration date on a backup file.
USE AdventureWorks2012; GO BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak' WITH EXPIREDATE = '6/30/2015' ;
You can use either the EXPIREDATE or RETAINDAYS (for which you provide a number) option in order to let SQL Server know when this backup file can be overwritten.
If no date or value is specified for this option, then the expiration date is determined from the media retention server configuration settings.
Using SSMS (SQL Server Management Studio)
You begin by connecting to the database that you want to create a backup of and extend the Databases folder in the Object Explorer window.
In the window that appears just select either a specific date when the backup should expire or a number of days when the backup file will be set as expired, since it has been created.
Like we’ve said before, in case you don’t specify a value, the default value which is set on the Default backup media retention in the Server Properties dialog box will be used.
You can find this value by right-clicking the server name in the Object Exporer window and selecting Properties.
In the window that will appear, go to the Database Settings page in order to see what is the default value for expiraton (in my case it’s 0).