About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.
Most backup reports say one thing: the job succeeded. For SQL Server, that means the backup statement ran to the end and wrote its output. It does not mean the file can be read back later, that every page inside it was sound when it was copied, or that the database it holds is internally consistent.
SQL Server provides three tools that answer those questions to different depths. They are often confused, and the weakest is sometimes treated as the strongest. This article sets out what Microsoft documents about each, and where each one stops.
Level 1: backup checksums
What they check
Microsoft's article on possible media errors during backup and restore explains that SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum.
When a backup is taken with checksums, two things happen:
- Before each page is written to the backup, SQL Server verifies the page's own checksum or torn-page information, if it has any. If it finds a page error, by default the backup fails.
- Separately, SQL Server calculates a backup checksum over the whole backup stream and stores it on the backup media.
Microsoft is clear that this process is read-only: BACKUP never modifies the source page on disk nor the contents of a page.
Where they stop
Two limits matter.
First, the page check depends on pages having verification information. Microsoft notes that if a page has neither a checksum nor torn-page detection, backup cannot verify the page, and it is included as is. In our reading, a database that has been upgraded over many versions, or whose page verification setting was changed, may contain pages that are never checked this way.
Second, a page with a valid checksum can still belong to a database with logical problems, such as damaged links between pages. The checksum shows a page was written and read back consistently; it does not show the database structure is correct.
When they are on
Microsoft's page Enable or disable backup checksums during backup or restore explains that checksums are requested with the CHECKSUM option and that not using them is the default behavior, except for a compressed backup. In other words, compressed backups use checksums by default; uncompressed ones do not unless asked.
The media errors article also notes the cost: checksums add CPU work, and Microsoft suggests monitoring the overhead and the effect on concurrent workloads. In our view, for most systems that cost is worth paying, because without a backup checksum the later checks have less to work with.
What happens when a backup continues past an error
By default, a page checksum error stops a backup. SQL Server can be told to continue instead. Microsoft lists what then happens: the backup set is flagged as containing errors, the page is recorded in msdb's suspect pages table, the error is logged, and the backup set is marked damaged in backup history. SQL Server then Issues a message that the backup was successfully generated, but contains page errors.
In our reading, that last message is a reason to check more than the job's success status. A monitoring process should look at the damage flag in backup history and at the suspect_pages table, which holds one row per page that failed with a minor 823 error or an 824 error. Microsoft notes that this table has a limit of 1,000 rows.
Level 2: RESTORE VERIFYONLY
What it checks
Microsoft's reference for RESTORE VERIFYONLY describes it as a command that Verifies the backup but does not restore it, checking that the backup set is complete and the entire backup is readable. The page says The goal is to be as close to an actual restore operation as practical.
Microsoft lists the checks it performs:
- That the backup set is complete and all volumes are readable.
- Some header fields of database pages, such as the page ID.
- The checksum, if present on the media.
- Whether there is enough space on the destination devices.
The media errors article adds that when a backup has backup checksums, restore and VERIFYONLY verify both the backup checksums and the page checksums by default. If there is no backup checksum, the same article says, the operation proceeds without checksum verification, because restore cannot reliably verify page checksums without it.
Where it stops
The same reference is direct about the limit: VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes.
In our reading, VERIFYONLY answers "can this file be read, and does it match what was written?" It does not answer "is the database inside it usable?" And the value of its answer depends heavily on whether the backup was taken with checksums. Without them, a VERIFYONLY success says little more than that the file is a complete, well-formed backup.
There is a second limit. VERIFYONLY runs on the server where it is issued. In our reading, a VERIFYONLY run on the server that took the backup does not prove another server can restore it: it does not test, for example, whether the encryption certificate for an encrypted backup exists elsewhere, or whether the restore target has the right version and space.
One more detail, from the media errors article: By default, after encountering a page checksum error, a BACKUP or RESTORE operation fails and a RESTORE VERIFYONLY operation continues. In our reading, that makes VERIFYONLY useful for finding out how much of a damaged backup is affected.
Level 3: a real restore, then DBCC CHECKDB
Why the restore matters
A real restore is the only check that exercises the whole path: reading every byte, rebuilding the database files, replaying the log to a consistent point and bringing the database online. If the restore needs anything the backup itself does not hold, such as a certificate, a credential to reach object storage or enough disk, a real restore finds out.
What CHECKDB adds
Microsoft's reference for DBCC CHECKDB says it Checks the logical and physical integrity of all the objects in the specified database. Run against a restored copy, it checks the database as a recovered system would see it.
The reference also describes a faster, narrower option. PHYSICAL_ONLY can make CHECKDB much quicker on large databases and is recommended for frequent use on production systems, but Microsoft adds that We still recommend that a full run of DBCC CHECKDB be performed periodically. It skips FILESTREAM data and column-integrity checks.
Two further points from the reference matter for backup testing:
- Memory-optimised tables. CHECKDB validates only disk-based tables. Microsoft says backup and recovery perform CHECKSUM validation for files in memory-optimised filegroups. Because repair options are not available for memory-optimised tables, Microsoft says you must back up your databases regularly and test the backups.
- Repair is not a substitute. Microsoft always recommends a user restore from the last known good backup as the main way to recover from CHECKDB errors. The option that repairs with data loss is described as an emergency last resort.
In our reading, that recommendation only works if there is a known good backup. A restored copy that passes the relevant CHECKDB checks provides database-integrity evidence. Business correctness, external dependencies and application behaviour still require separate checks.
Running CHECKDB on the restored copy
Running CHECKDB on a restored copy rather than on production has an obvious attraction: the load falls on the test server. In our reading, a clean CHECKDB on a restored copy shows the database was consistent at the time of the backup. It does not show the production database's storage is healthy now, because a fault that appears after the backup, or a storage fault that the backup did not read, will not be in the copy. We recommend treating restored-copy CHECKDB as backup evidence, and keeping regular checks on production as well, at whatever depth its workload allows.
Use the test to measure, not just to pass
A test restore produces a second piece of evidence that is easy to throw away: how long it took. The time from starting the restore to a database that passes its checks and answers queries is the closest available measure of how long a real recovery of that database would take, on that kind of hardware, from that backup location.
In our view, that figure deserves the same care as the pass or fail result. We recommend recording, for each test:
- The time to copy or reach the backup files from where they are stored.
- The time for the restore itself, including any log backups applied.
- The time for CHECKDB, or for whatever check the organisation accepts before users return.
- Anything that had to be found, fixed or requested during the test, such as a missing certificate, a credential or disk space.
Compared with the recovery time the business expects, those figures show whether the backup design is fast enough as well as sound. The last item is often the most useful: each thing that had to be found during a calm test is something that would have had to be found during a real outage.
Combining the three
The three levels answer different questions:
| Check | What it shows | What it does not show |
|---|---|---|
| Backup with CHECKSUM | Available page verification information passed; the backup stream has a checksum | Pages without verification; logical structure |
| RESTORE VERIFYONLY | Backup set complete, readable, checksums match | Database structure; that another server can restore it |
| Restore + DBCC CHECKDB | The tested backup restores and passes the selected database integrity checks | That production is still healthy now |
From the points above, these are our recommendations:
- Take backups with checksums, and check backup history for damage flags, not just job status.
- Run VERIFYONLY routinely as a cheap check that backups are complete and readable, ideally on a server other than the one that took them.
- Restore regularly to a separate server, from the backup location a real recovery would use, with only what a real recovery would have.
- Run DBCC CHECKDB on the restored copy, at full depth at least some of the time.
- Record the result with the backup's identity, date and time taken, so "last known good" is a fact, not an assumption.
- Cover every type: full, differential and log backups together, restored as a chain to a point in time.
Make the evidence specific enough to repeat
A validation record should identify the exact backup set and all required stripes, the full and differential base, the log interval restored, and the target SQL Server build. Record whether checksums were present and actually checked. Do not infer their presence solely from a normal default, because backup options and server configuration can change it. Preserve warnings and damage flags alongside the final command result.
Then record the scope of CHECKDB: a full check and PHYSICAL_ONLY are different evidence, and memory-optimised content has separate limits. Add an application-level read or reconciliation agreed with the data owner, using an isolated copy. A structurally valid database can still contain a legitimate but unwanted deletion. The recovery point must precede that change and meet the business’s acceptable data loss. Reusing a previous pass after a different backup, target or key arrangement changes is not proof of the new recovery path.
Risks and limits of this article
- Based on Microsoft documentation as read on 11 November 2026. Defaults can change between versions.
- The limits of each check and the recommendations are our reading of Microsoft's pages, labelled as such.
- No backups, restores or DBCC commands were run for this article, and syntax is deliberately not reproduced.
- Running CHECKDB or restores has resource costs; size test servers and schedules for the databases involved.
Microsoft describes REPAIR_ALLOW_DATA_LOSS as an emergency last resort and explicitly says it is not an alternative to restoring a known-good backup. That warning does not turn a repair attempt into backup acceptance evidence.
Sources
Writer research: 11 November 2026. Independently reopened for review: 13 November 2026.
- Microsoft Learn: Possible media errors during backup and restore (SQL Server)
- Microsoft Learn: Enable or disable backup checksums during backup or restore
- Microsoft Learn: RESTORE statements - VERIFYONLY (Transact-SQL)
- Microsoft Learn: DBCC CHECKDB (Transact-SQL)
- Microsoft Learn: suspect_pages (Transact-SQL)
This article was originally published on .