Successful backup messages do not certify every part of the database. WITH CHECKSUM adds useful validation while SQL Server reads and writes the backup. Its limits still belong in your recovery plan.
Separate Page Checks from Database Integrity
I ask what a backup validation command proves before relying on its success message. Different checks inspect different layers. Backup checksums and database integrity checks aren't interchangeable.
BACKUP WITH CHECKSUM validates existing page checksums as pages are read. It also computes a separate check over the backup stream as a whole. Those checks detect particular forms of damaged or altered data.
A page written before page checksums were enabled doesn't gain historical protection merely because today's backup requests checksums. SQL Server checks protection information that exists. It doesn't reconstruct checksums that were never written.
Logical consistency needs another kind of examination. Checksums can match bytes that contain a logically inconsistent structure. DBCC CHECKDB examines database consistency beyond the backup stream.
Keep those layers visible in the runbook. A backup without a restore rehearsal leaves another gap. Recovery needs readable media and a usable restored database.
Inspect the Database's Page Protection
PAGE_VERIFY CHECKSUM is the normal page-protection choice to review. Inspect the current setting first. Changing it affects checksums written as pages are subsequently modified and written.
The change doesn't immediately rewrite every old page. That makes existing protection coverage an important limitation. Don't describe the setting as a retroactive repair.
Use the query below to inspect a selected database. Replace the sample name with the authorized database. It doesn't change the setting or start a backup.
A reported mismatch is evidence to investigate. It isn't an instruction to discard the only backup you have. Preserve the failed operation's messages and relevant media during the incident review.
Also distinguish an expected verification policy from observed coverage. The configuration tells you what the engine is configured to write. It doesn't count every protected page for you.
SELECT name, page_verify_option_desc, recovery_model_desc
FROM sys.databases WHERE name = N'ChecksumDemo';
SELECT name, value, value_in_use
FROM sys.configurations WHERE name = N'backup checksum default';Request BACKUP WITH CHECKSUM Explicitly
The next command assumes a disposable ChecksumDemo database already exists. It also assumes the SQL Server service can write to the placeholder directory. Replace the path with a fresh test filename.
COPY_ONLY keeps this demonstration full backup from changing the differential base. The option after it requests the validation explicitly. The command reports its own pages and duration when it finishes.
Don't add CONTINUE_AFTER_ERROR casually to make a failing backup finish. Its purpose belongs in a documented salvage decision. A backup known to contain damage must remain labeled accordingly.
An existing filename can already contain backup sets. Use a new destination for the rehearsal. The example avoids formatting or initializing an existing device.
That option is valuable because it examines bytes during the operation. It doesn't remove the need to monitor the command's final status. A failed write and a failed page validation are both failed backup work.
BACKUP DATABASE [ChecksumDemo]
TO DISK = N'C:\SqlBackups\ChecksumDemo-new-test.bak'
WITH COPY_ONLY, CHECKSUM;
Read the Backup History Flag
msdb.dbo.backupset records whether a backup set has backup checksums. Inspect has_backup_checksums rather than assuming a job name proves its options. Read database and completion time beside the flag.
The history can include damaged backup sets. is_damaged provides another field to review. A completed row doesn't erase that status.
The media path is recorded in backupmediafamily. Joining through media_set_id connects the history to its destination. A striped backup has several media-family rows for the same set.
History describes recorded backup operations. It doesn't prove the current file still exists or remains readable. Retention scripts and storage failures can change the media afterward.
I inspect actual history after changing a backup policy. The intended command and the executed command sometimes differ. Readback closes that gap.
SELECT TOP (30) bs.backup_set_id, bs.database_name, bs.type,
bs.backup_start_date, bs.backup_finish_date,
bs.has_backup_checksums, bs.is_damaged, mf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS mf ON mf.media_set_id = bs.media_set_id
WHERE bs.database_name = N'ChecksumDemo'
ORDER BY bs.backup_finish_date DESC, bs.backup_set_id DESC;Verify Restore Media WITH CHECKSUM
RESTORE VERIFYONLY checks that the backup is complete and readable for its supported validation scope. Adding the same option makes the restore validate checksums. Use the exact backup set and all media required for that set.
The FILE option selects the set position on a media file. Inspect RESTORE HEADERONLY when the file contains several sets. Don't assume the first set is the one your job produced.
If the requested checksums aren't present, explicit verification doesn't invent them. Treat that as a policy or selection problem. Confirm you are validating the intended backup.
VERIFYONLY doesn't restore database files and perform the same work as CHECKDB. A successful result therefore isn't a complete logical consistency test. It also doesn't test application access after recovery.
The following commands use the test path from the backup example. Replace FILE with the selected position from the header. Keep the original messages with the rehearsal record.
RESTORE HEADERONLY FROM DISK = N'C:\SqlBackups\ChecksumDemo-new-test.bak';
RESTORE VERIFYONLY FROM DISK = N'C:\SqlBackups\ChecksumDemo-new-test.bak'
WITH FILE = 1, CHECKSUM;Review the WITH CHECKSUM Instance Default
The instance-wide default for backup checksums requests this behavior by default when an operation doesn't specify its own choice. It applies at the instance level. An explicit NO_CHECKSUM can override the default.
Review all backup paths before adopting that policy. Some administrative utilities don't expose the option directly. The default supplies coverage for those paths, subject to explicit overrides.
The command below changes a server setting when executed. Use it only in a test instance or an approved policy change. Don't run it on a shared server as part of a reading exercise.
Record the existing value before the change. Recheck value_in_use afterward. Then inspect newly written backup history rather than stopping at the configuration readback.
Which operation in your backup scripts still carries that explicit override? Search those definitions as part of the review. A good default cannot overrule an explicit exception.
EXEC sys.sp_configure N'backup checksum default', 1;
RECONFIGURE;
SELECT value, value_in_use FROM sys.configurations WHERE name = N'backup checksum default';Keep Restore and CHECKDB in the Schedule
Restore a representative backup onto an isolated server using separate database and file names. Run DBCC CHECKDB on that restored database. Preserve its messages and the selected backup identity.
That tests the backup's recoverability and the consistency of the restored contents. It doesn't prove production storage remained healthy after the backup completed. Continue production monitoring and the appropriate integrity schedule.
I treat backup verification as one layer in that routine. I don't let its convenient success message replace a restore test. The restore plan needs evidence that users can recover their work.
Page and stream validation improves the backup operation's evidence. Combine it with checked history, readable media and restored consistency testing. The backup file isn't impressed by the confidence of its filename.
Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and SQL SERVER 2022: Last Valid Restore Time: Improved Backup Metadata.
A backup checksum is not a complete integrity certificate, it is protection for a specific layer of the recovery process.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
