Restore Testing: Proving the Backups Actually Work

Most backup monitoring answers one question: did a backup happen recently. That is worth knowing and it is not the same as knowing you can recover. A job can succeed, write a file, record it in msdb and produce something that will not restore, and nothing in the success path notices.

Three different checks get confused with each other, and they prove three different things.

Your RTO Is Unmeasured Until You Have Timed a Restore

Recovery point objective is a property of your backup schedule and you can calculate it on paper. Recovery time objective is a property of how long a restore actually takes on your hardware, with your file sizes, over your storage, and it cannot be calculated on paper with any confidence.

A four-hour RTO agreed in a meeting is a number somebody wrote down. A four-hour RTO backed by a restore you timed last month is a commitment. The gap between those two is usually discovered at the worst possible moment.

Running a Drill Without Touching Production

The safe shape of a restore test is to restore to a different name, on a server you are allowed to disturb, using WITH MOVE so the files land somewhere harmless. Nothing about this touches the source database:

RESTORE DATABASE Sales_RestoreTest
FROM DISK = 'D:\Backup\Sales_FULL.bak'
WITH MOVE 'Sales'     TO 'E:\RestoreTest\Sales_RestoreTest.mdf',
     MOVE 'Sales_log' TO 'E:\RestoreTest\Sales_RestoreTest_log.ldf',
     RECOVERY, STATS = 5;

Two things are worth doing immediately afterwards, because a restore that completes is not the same as a database that is sound. Run a consistency check on the restored copy, and compare a row count or two against something you expect:

DBCC CHECKDB (Sales_RestoreTest) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Running CHECKDB against the restored copy rather than the live database is a genuine side benefit of the drill: you get the consistency check without putting the read load on production.

What To Write Down

A drill that leaves no record proves something once and nothing thereafter. Four numbers are worth keeping every time, because they turn one restore into a trend:

• Backup size and restore duration, which together give you a throughput figure you can extrapolate to the databases you have not tested.
• Which backup you used, so a failure can be traced to a specific file rather than to “the backups”.
• Whether CHECKDB came back clean on the restored copy.
• Anything you had to look up mid-restore. The point of a drill is partly to find out which steps you do not actually know, while it is safe not to know them.

How Often

Often enough that the procedure is familiar and the numbers are current, which for most estates means quarterly for the databases that matter and at least once for everything else. The trigger worth adding is a change-driven one: after a storage change, a version upgrade, or a significant growth in database size, the timing you measured before no longer describes the system you have.

Common Questions