Fix 'Restore Validation Check Failed' in SQL Server

The Problem

You're restoring a SQL Server database backup and get this:
Msg 3154, Level 16, State 4, Line 1
Restore is terminating abnormally. The backup set was written with CHECKSUM, but the restore operation has detected corruption in the backup set.

This happens when the backup file itself got corrupted – maybe a bad sector on disk, a network glitch during transfer, or someone pulled the cable mid-copy. SQL Server's RESTORE with CHECKSUM validates every page as it reads. When a page doesn't match its stored checksum, restore stops dead.

Here's the fix path: try the simplest override first. If data still loads, you're done. If not, try fixing specific pages. Last resort: you rebuild from a known-good backup.

Quick fix (30 seconds) – force restore without validation

Skip the checksum check. Only do this if you're willing to risk some corrupted rows. Most times the corruption is in an unused part of a page, and the actual data comes through fine.

RESTORE DATABASE YourDatabase
FROM DISK = 'D:\Backups\YourDatabase.bak'
WITH REPLACE, RECOVERY, NO_CHECKSUM;

Why this works: NO_CHECKSUM tells SQL Server not to verify each page's checksum. It just writes the pages as-is. If only a few bytes are corrupted, SQL Server might still load the database. After restore, run DBCC CHECKDB to find corruption.

But be careful: If the corruption is in a critical page (like the boot page or a system catalog), the database won't mount. You'll get a different error: Msg 824 or Msg 825. In that case, skip to the moderate fix.

Moderate fix (5 minutes) – restore specific pages from backup

If the database fails to mount after NO_CHECKSUM, or DBCC CHECKDB reports corruption, you can restore just the damaged pages. This works when only a few pages are broken – not the whole backup.

1. Find which pages are corrupt:
   DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;
   Look for lines like: “(1 row affected) – Page (1:23456) of database ID 7 is corrupt.” Note the page ID (file:page format, e.g. 1:23456).
2. Restore only those pages from backup:
   

   RESTORE DATABASE YourDatabase
   PAGE = '1:23456'
   FROM DISK = 'D:\Backups\YourDatabase.bak'
   WITH NORECOVERY;
   

   You can list multiple pages: PAGE = '1:23456, 1:78901'.
3. Bring the database online:
   RESTORE DATABASE YourDatabase WITH RECOVERY;

The trick: Page restore only works if the backup is a full backup (not a differential or log backup). Also, the backup must have been taken with WITH CHECKSUM – otherwise the backup doesn't have checksums to verify. If you don't know, check with RESTORE VERIFYONLY FROM DISK = '...' WITH CHECKSUM;. If it says “checksum not present”, you can't do page restore – go straight to the advanced fix.

Advanced fix (15+ minutes) – rebuild from clean backup

If the above fails – meaning the backup itself is too damaged – you need a different backup. Find a backup taken before the corruption happened. If you don't have one, you're looking at data loss.

1. Check your backup history:
   SELECT * FROM msdb.dbo.backupset WHERE database_name = 'YourDatabase' ORDER BY backup_finish_date DESC;
   Identify a backup that's older but not corrupted. Test it:
   RESTORE VERIFYONLY FROM DISK = 'D:\Backups\OlderBackup.bak' WITH CHECKSUM;
   If it passes, restore that.
2. If no clean full backup exists, but you have transaction log backups, you can restore the last clean full backup (even if partial) then apply log backups up to the point of failure. This is called a point-in-time restore.
   

   RESTORE DATABASE YourDatabase
   FROM DISK = 'D:\Backups\LastCleanFull.bak'
   WITH NORECOVERY;
   
   RESTORE LOG YourDatabase
   FROM DISK = 'D:\Backups\LogBackup_20250320.trn'
   WITH RECOVERY, STOPAT = '2025-03-20 14:30:00';
   

3. If all backups are corrupt and you have no clean copy, your only option is to restore from a different server's backup (if you had log shipping or mirroring) or accept the corruption. At this point, run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS as a last resort – but that deletes corrupt rows.

Real-world scenario that triggers this: A DBA copies a backup file from an offsite NAS over a slow VPN. The transfer drops some packets, and the file ends up with a few wrong bytes. The backup appears fine by size, but SQL Server's checksum catches it.

Prevention: test your backups regularly

The real fix is catching this before you need it. Schedule weekly RESTORE VERIFYONLY WITH CHECKSUM against a random backup from each database. If it fails, you know you need to take a fresh backup immediately.

-- Example scheduled verification
RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\YourDatabase.bak'
WITH CHECKSUM;

Also consider using BACKUP ... WITH CHECKSUM when creating backups. It adds about 2-3% overhead but gives you the validation that caught this error.