Untested Backup: The Joke That Ends With a Resignation Letter

Years ago I read a forum thread where a developer lost a database and then learned the backups would not restore. The replies were so dark they were funny, and one of them suggested writing a resignation letter. An untested backup is the setup for exactly that joke.

How the Untested Backup Joke Goes

The backup job ran green every night for months. Nobody ever restored anything, because nothing ever broke. Then something broke.

The file was there. It was also a backup of the wrong database, or it was on the same disk that just died, or it stopped growing six weeks ago when someone changed a path. An untested backup always looks fine right up to the moment you need it.

Start with a quick look at when each database last had a full backup. The answer surprises someone on almost every server I review.

SELECT d.name AS DatabaseName,
       MAX(b.backup_finish_date) AS LastFullBackup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
    ON b.database_name = d.name
   AND b.type = 'D'
WHERE d.name <> 'tempdb'
GROUP BY d.name
ORDER BY LastFullBackup;

A NULL in the second column means no full backup was ever recorded for that database on this server. Those rows go to the top of the list, and to the top of your day.

A Green Job Is Not a Restore

A successful backup job means SQL Server wrote a file. It does not mean you can get your data back from that file. Two small options help, and they belong in every backup script:

BACKUP DATABASE SalesDemo
TO DISK = N'D:\Backup\SalesDemo.bak'
WITH CHECKSUM, INIT;

RESTORE VERIFYONLY
FROM DISK = N'D:\Backup\SalesDemo.bak'
WITH CHECKSUM;

WITH CHECKSUM makes SQL Server check page checksums while it writes the backup. RESTORE VERIFYONLY checks that the backup set is complete and readable. Both are good. Neither one is a restore.

Run a Small Restore Drill

Once a month, restore the latest backup under a different name on a test server. Then check it. Your logical file names can differ, and RESTORE FILELISTONLY shows them.

RESTORE DATABASE SalesDemo_Drill
FROM DISK = N'D:\Backup\SalesDemo.bak'
WITH MOVE N'SalesDemo' TO N'D:\Data\SalesDemo_Drill.mdf',
     MOVE N'SalesDemo_log' TO N'D:\Data\SalesDemo_Drill_log.ldf',
     CHECKSUM, STATS = 10;

DBCC CHECKDB (SalesDemo_Drill) WITH NO_INFOMSGS;

Write down how long the restore took. That number is your real recovery time, and your manager will ask for it on the worst day of the year. Count rows in two or three important tables and compare them with production.

Write the Untested Backup Out of the Story

Keep one copy of the backups off the server. Put the drill on a calendar, not in your memory. Keep a simple log with the date, the file, the restore time and the result.

After a few months, that log is the most comforting document in the building. The resignation letter can stay a joke.

Related reading on this blog: SQL SERVER 2022: Last Valid Restore Time: Improved Backup Metadata and Undo Human Errors in SQL Server: SQL in Sixty Seconds #109: Point in Time Restore.

Restore something this month, even something small. You will sleep better, and so will your boss.

A backup is not a file in a folder, it’s a restore you have already practiced.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.