Backup & Recovery from First Principles, Part 8: Verification – Your Backup Job Succeeding Proves Almost Nothing
Part 7 ended on an uncomfortable question: the “clean” full backup I used to repair a corrupted page – how did I know it was clean? This part demonstrates, with a deliberately corrupted database, exactly how little a successful backup job proves, what CHECKSUM and VERIFYONLY each actually verify, and what real verification looks like.
This is part 8 of the backup and recovery series (part 1). Demo ran on SQL Server 2019 (CU32) and behaves identically on SQL Server 2025 (RTM), against a database with one page corrupted via the DBCC WRITEPAGE technique.
Exhibit A: Backing Up a Corrupt Database Succeeds
The database has a page whose checksum is wrong – any read of it throws Msg 824. Watch what a plain backup does:
|
1 2 3 |
BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\verify_nochecksum.bak' WITH INIT, COMPRESSION; |
|
1 2 3 |
Processed 121208 pages for database 'bkrec_demo', file 'bkrec_demo' on file 1. Processed 2 pages for database 'bkrec_demo', file 'bkrec_demo_log' on file 1. BACKUP DATABASE successfully processed 121210 pages in 1.379 seconds. |
Success. No warning, no error. A backup is a physical page copy: it reads pages off disk and writes them to the backup file, and by default it does not validate page checksums along the way. The corruption rode along into the backup file, faithfully preserved. Every generation of backups from now on contains it, and your retention policy is quietly aging out the last backup that predates the damage.
Exhibit B: RESTORE VERIFYONLY Blesses the Corrupt Backup
Surely the verification command catches it?
|
1 |
RESTORE VERIFYONLY FROM DISK = N'C:\temp\bkrec\verify_nochecksum.bak'; |
|
1 |
The backup set on file 1 is valid. |
“Valid.” VERIFYONLY checks that the backup file is complete and readable: header fields, file structure, that sort of thing.[1] Critically, it can only validate page checksums if the backup was taken WITH CHECKSUM – and this one was not, so there was nothing for it to check. It answered “is this file a well-formed backup?” and everyone heard “is my data okay?”
Exhibit C: WITH CHECKSUM Catches It at Backup Time
|
1 2 3 |
BACKUP DATABASE [bkrec_demo] TO DISK = N'C:\temp\bkrec\verify_checksum.bak' WITH INIT, COMPRESSION, CHECKSUM; |
|
1 2 3 4 |
Msg 3043, Level 16, State 1 BACKUP 'bkrec_demo' detected an error on page (1:113472) in file 'bkrec_demo'. Msg 3013, Level 16, State 1 BACKUP DATABASE is terminating abnormally. |
WITH CHECKSUM makes the backup validate every page’s existing checksum as it reads it, and write a checksum over the whole backup stream.[2] The corrupt page fails validation and the backup fails loudly – which is precisely what you want. A failed backup at 2 AM is an alert; a corrupt backup discovered during a 2 AM restore is a resume-generating event. This is the difference between finding corruption on your schedule versus the disaster’s schedule.
Two prerequisites make this work:
- The database must have page checksums to validate.
PAGE_VERIFY CHECKSUMis the default for databases created on SQL Server 2005+, but databases migrated from older versions or set toNONE/TORN_PAGE_DETECTIONgive backup checksums little to verify at the page level. Checksys.databases.page_verify_option_desc. - Someone has to write
WITH CHECKSUM. It is not the default for backups (backup compression’s default server setting does not enable it either). Put it in every backup command you own. The CPU cost is small; the information value is enormous. Trace flag 3023 makes it the default server-wide, if changing every job is impractical.
With a checksummed backup in hand, RESTORE VERIFYONLY ... WITH CHECKSUM finally has teeth: it re-validates every page checksum in the file without restoring it.
What Even CHECKSUM Cannot Prove
Page checksums detect what the I/O path scrambled after SQL Server last wrote the page. They do not detect logical corruption (bad index linkage, wrong metadata), memory corruption checksummed on its way to disk, or the numerous problems only DBCC CHECKDB finds. The verification ladder, each rung strictly stronger:
- Backup job succeeded – a file exists.
- WITH CHECKSUM succeeded – pages passed I/O-level validation at read time.
- RESTORE VERIFYONLY WITH CHECKSUM – the file is structurally sound and pages still validate.
- Actual test restore – the backup demonstrably restores. This is the only rung that proves the thing you actually care about.
- Test restore + DBCC CHECKDB – the restored database is logically consistent too. As a bonus, offloading CHECKDB to a restored copy takes the load off production.
Automate rung 5. A spare server (or spare instance – licensing for a dedicated restore-test box is cheap next to data loss) restores last night’s backups on a schedule, runs CHECKDB, and reports. Generating restore commands from msdb history does the heavy lifting; the three-layer backup strategy and why local-only backups equal no backups cover where those verified files should live. And remember from part 3: verification also means verifying the chain – all the files, not just each file.
Next: part 9 benchmarks the backup tuning knobs almost nobody touches: BUFFERCOUNT, MAXTRANSFERSIZE, BLOCKSIZE, and striping.
When did a backup last lie to you? Comments below, or find me on Bluesky or LinkedIn.
References
- RESTORE VERIFYONLY (Transact-SQL) – Microsoft Learn. What VERIFYONLY checks, and its dependence on backup checksums for page validation. ↩
- Possible Media Errors During Backup and Restore (SQL Server) – Microsoft Learn. Backup checksums, page-checksum validation during backup, and error response options. ↩