How to set expiration date on a backup

by
set expiration date backup
The way you set the expiration date on a backup for SQL Server databases is quite easy. 
You have two options for doing this. In order to find out, read the following article where we will show you both options.

As you might know already, the expiration date on a backup is the date (or a 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 that 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 to 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.

set expiration date on a backup

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.

set expiration date on a backup

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 Explorer window and selecting Properties.

set expiration date on a backup

In the window that will appear, go to the Database Settings page in order to see what is the default value for expiration (in my case it’s 0).

set expiration date on a backup

Leave a Comment