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.
| Check | What it proves, and what it leaves open |
|---|---|
| Backup coverage | A backup happened in the window. Not that the file is readable, or still there. |
| Chain integrity | The log chain is unbroken. Not that any of those files will read. |
RESTORE VERIFYONLY | The backup set is readable. Not that the database inside is free of corruption. |
| An actual restore | The 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_ONLYfor 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?
We use a third-party backup tool. Does that change anything?
Do I need to test every database?
What if the restore fails?
Can I test the log chain without a full restore?
A drill is only the start. These are the checks and the mechanics behind it.
- How to Restore a Database in SQL Server, the mechanics of the restore itself.
- Get Backup Restore Duration Estimate, how long a restore should take, from your own backup history, before you start it.
- Get Backup Chain Integrity, LSN continuity per database, the cheap check between drills.
- DBCC CHECKDB Found Corruption, if the consistency check on the restored copy comes back dirty.
- RESTORE VERIFYONLY Fails: Reached the End of the File (Error 3203), if the drill stops at the VERIFYONLY step because the backup file is short.
- Backups and Recovery, the coverage and chain-integrity scripts referred to above.
Leave a Reply