Skip to content
Home » Expiration Date on Database Backups

Expiration Date on Database Backups

What does the expiration date mean on SQL Server database backups?

In SQL Server Management Studio, there’s an option in the Back Up Database wizard which explicitly provides an option for “Backup set will expire”. But until this week, I had never given it much thought.

This feature has been there for as long as I can remember. Does it mean that we can’t restore databases if the expiry date has passed? Maybe it has something to do with backing up to tape.

Let’s find out.

Test 1: Restore a database with an old expiry date

Using the EXPIREDATE option as specified in SQL Server Books Online, we will perform a simple backup using last week Saturday as our parameter.

USE [master];
GO
CREATE DATABASE [BackupExpiryTest];
GO
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'
WITH EXPIREDATE = N'2016-04-29 00:00:00';
GO

Now let’s see if restoring the database today, 3 May 2016, still works. To be extra sure, we will drop the database we’ve just backed up.

USE [master];
GO
DROP DATABASE [BackupExpiryTest];
GO
RESTORE DATABASE [BackupExpiryTest]
FROM DISK = N'D:\Temp\BackupExpiryTest.bak';
GO

Processed 344 pages for database 'BackupExpiryTest', file 'BackupExpiryTest' on file 1.
 Processed 2 pages for database 'BackupExpiryTest', file 'BackupExpiryTest_log' on file 1.
 RESTORE DATABASE successfully processed 346 pages in 0.068 seconds (39.723 MB/sec).

The EXPIREDATE option has no effect on whether we can restore the database.

What is it for, then? Let’s go and read Books Online, specifically the BACKUP command.

In the section labelled Media Set Options, we see the following explanation:

{ NOINIT | INIT }
Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Even further down, under INIT, we see the following:

By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

  • Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.

Now we’re getting somewhere. If you back up your database to an existing file, the default setting (NOINIT) is to append that backup to the file. If you explicitly use INIT, however, it will overwrite the backup file and delete any existing backups, unless the EXPIREDATE value is a date sometime in the future.

Test 2: Append to existing backup file

Let’s append to our existing backup and set this backup to expire on Friday this week:

USE [master];
GO
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'
WITH EXPIREDATE = N'2016-05-07 00:00:00';
GO

Processed 344 pages for database 'BackupExpiryTest', file 'BackupExpiryTest' on file 2.
 Processed 2 pages for database 'BackupExpiryTest', file 'BackupExpiryTest_log' on file 2.
 BACKUP DATABASE successfully processed 346 pages in 0.055 seconds (48.161 MB/sec).

Notice that it says “file 2” in the output. This indicates that the backup we just performed is the second one in the backup file (or media set). If we wanted to restore this particular backup, we would have to specify WITH FILE = 2 in the restore script.

Now let’s try overwriting everything in the existing backup file using the INIT option:

USE [master];
GO
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'
WITH INIT;
GO

Msg 4030, Level 16, State 1, Line 1
The medium on device 'D:\Temp\BackupExpiryTest.bak' expires on May  7 2016 12:00:00:000AM and cannot be overwritten.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Oops.

The big hammer

If we look further down the Books Online entry about BACKUP, we will see the following:

To override these checks, use the SKIP option.

Sure, let’s try overriding it:

USE [master];
GO
BACKUP DATABASE [BackupExpiryTest]
TO DISK = N'D:\Temp\BackupExpiryTest.bak'
WITH INIT, SKIP;
GO

Processed 344 pages for database 'BackupExpiryTest', file 'BackupExpiryTest' on file 1.
 Processed 2 pages for database 'BackupExpiryTest', file 'BackupExpiryTest_log' on file 1.
 BACKUP DATABASE successfully processed 346 pages in 0.056 seconds (48.139 MB/sec).

Summary

If you want to store multiple database backups in a single file, and want to control when these backups can be overwritten, you can set an expiration date with EXPIREDATE. Then the INIT option overwrites any backups that have not yet expired.

However, this can easily be overridden by the SKIP command. So this raises the question: if using the SKIP option simply overrides any expiration date in the backup file, why use EXPIREDATE in the first place?

I have never used expiration dates with my backups, and I don’t think I’m going to start now. I see no benefit in reusing existing files to append database backups. I will stick to using just one database backup per file, with a strong naming convention (including the date and time of the backup).

This way, dates in the file name make it easy to see at a glance when backups were taken, and then I have a higher confidence rating that files can be managed without having to scan their contents. Additionally, files older than a certain date can be cleaned out by a maintenance plan.

Tags: