Restore Testing: Proving the Backups Actually Work

⚡A backup you have never restored is a hypothesis. Coverage checks prove a file was written, chain checks prove the sequence is unbroken, and neither proves the database inside will come back. Only a restore does that, and the first time you run one should not be during an incident.

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.

CheckWhat it proves, and what it leaves open
Backup coverageA backup happened in the window. Not that the file is readable, or still there.
Chain integrityThe log chain is unbroken. Not that any of those files will read.
RESTORE VERIFYONLYThe backup set is readable. Not that the database inside is free of corruption.
An actual restoreThe database comes back, and how long it takes. This is the one that settles it.

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 VERIFYONLY
FROM DISK = 'D:\Backup\Sales_FULL.bak';

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;

Run RESTORE VERIFYONLY first because it is cheap and fails fast on an unreadable file. If the restore is long and you want to watch it, Get Backup and Restore Progress shows the percentage and the estimated finish from another session.

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 and a total against the source:

DBCC CHECKDB (Sales_RestoreTest) WITH NO_INFOMSGS, ALL_ERRORMSGS;

SELECT 'source' AS copy, COUNT_BIG(*) AS order_rows, SUM(Amount) AS amount_total FROM Sales.dbo.Orders
UNION ALL
SELECT 'restored', COUNT_BIG(*), SUM(Amount) FROM Sales_RestoreTest.dbo.Orders;

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 a Clean Drill Looks Like

I ran this on a lab instance (SQL Server 2025, Enterprise Developer) against a 1,000,000-row table, about 236 MB of data. The backup was compressed.

The backup set on file 1 is valid.
RESTORE DATABASE successfully processed 30050 pages in 5.424 seconds (43.282 MB/sec).

restore_ms
-----------
       6171

(DBCC CHECKDB: no output, which with NO_INFOMSGS means clean)

copy      order_rows   amount_total
--------  -----------  ------------
source        1000000  500261077.16
restored      1000000  500261077.16

backup_mb     compressed_mb   backup_ms
236.09        22.30           1000

Real example output from a lab instance. The restore took about six seconds, CHECKDB was silent, and the row count and total match the source. That gives you three of the four numbers to write down. A lab restore this small proves the method, not your RTO: the restore rate is the figure to carry forward, and only your own storage can supply it.


Large Databases: Estimate First, and Move the Work Off the Primary

For a small database the drill is a coffee break. For a multi-terabyte one it is a day of I/O, and you want to know that before you start rather than after. Before you restore, run Get Backup Restore Duration Estimate. It reads your own backup history in msdb and gives the throughput you have actually achieved, so size divided by rate gives an honest window to plan against.

The drill itself should not run on the production primary. Restore to a scratch or test server with the disk for it. If you run an availability group, two further options are worth considering:

  • Take the backup from a secondary. Backups on a secondary replica must be COPY_ONLY for a full backup, and the backup preference setting decides which replica is preferred. This moves the backup read load off the primary. See the MS Docs page on backups on secondary replicas.
  • Do the test restore on a secondary replica’s instance, if it has the space. A restore under a different name is just another database to that instance, so the primary is untouched. Watch the disk and the redo queue while it runs, because a big restore competes with redo for the same storage. Get AG Failover Readiness and Readable Secondary Usage shows how the secondary is already being used.

Two limits apply. Readable secondaries and backups on a secondary need an availability group with a readable replica, which in practice means Enterprise edition. Basic availability groups on Standard do not offer either. And a CHECKDB that passes on a secondary proves that replica’s own copy of the pages, not the primary’s, so it does not replace one on the restored backup.


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, and which the duration estimate script will then read back to you.
  • 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.


Frequently Asked Questions

Is RESTORE VERIFYONLY enough on its own?
It is worth doing and it is not sufficient. It checks the backup set is readable and internally consistent; it does not read the pages of the database inside, so a backup of an already-corrupt database passes it. Pair it with regular CHECKDB on the source, and with a real restore periodically.
We use a third-party backup tool. Does that change anything?
Not the argument. It changes the syntax and often adds its own verification step, which is still verification of the file rather than proof of recovery. The question “have we restored one” is the same question regardless of what wrote the file.
Do I need to test every database?
No, and trying to is how the practice dies. Test the ones where recovery time is a commitment to somebody, plus a sample of the rest. A throughput figure from one large database tells you a lot about the others on the same storage.
What if the restore fails?
Then you have found the thing you were looking for, at a time when it costs you an afternoon rather than a business. Write down what failed, which is the point of keeping the numbers. Read the error above the closing message rather than the closing message itself, which is usually the least useful line in the output.
Can I test the log chain without a full restore?
Partly. A chain integrity check confirms the LSN sequence is unbroken, which is a real and cheap check worth automating. It tells you point-in-time recovery is possible on paper, not that the files will read.

Where To Go Next

A drill is only the start. These are the checks and the mechanics behind it.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *