Introduction SQL Server backup checksums verify the integrity of backup files. When a checksum mismatch is detected during restore verification, it indicates the backup file has been corrupted—either during creation, storage, or transfer. Using a corrupted backup for restore can result in a corrupted database.

Symptoms - `RESTORE VERIFYONLY` returns `The backup set on file 1 is valid` fails - Error 3189: `Damage to the backup set was detected` - `RESTORE HEADERONLY` shows backup completed but checksum verification fails - Error 3241: `The media family on device is incorrectly formatted` - Restore process aborts with checksum validation errors

Common Causes - Backup file corrupted during transfer (FTP, network copy, S3 upload) - Storage media corruption on the backup destination - Backup created without `CHECKSUM` option, unable to verify integrity - Disk sector errors on the source database during backup creation - Backup file truncated or modified after creation

Step-by-Step Fix 1. **Verify the backup file integrity": ```sql -- Check backup header RESTORE HEADERONLY FROM DISK = N'D:\Backups\mydb_full.bak';

-- Verify the backup (without restoring) RESTORE VERIFYONLY FROM DISK = N'D:\Backups\mydb_full.bak' WITH CHECKSUM;

-- Check file integrity outside SQL Server -- Compare checksum/hash with the original if available certutil -hashfile D:\Backups\mydb_full.bak SHA256 ```

  1. 1.**Attempt restore with CONTINUE_AFTER_ERROR (last resort)":
  2. 2.```sql
  3. 3.-- This may produce a corrupted database
  4. 4.RESTORE DATABASE mydb
  5. 5.FROM DISK = N'D:\Backups\mydb_full.bak'
  6. 6.WITH CONTINUE_AFTER_ERROR, RECOVERY,
  7. 7.MOVE 'mydb' TO 'E:\Data\mydb.mdf',
  8. 8.MOVE 'mydb_log' TO 'E:\Logs\mydb_log.ldf';

-- Immediately run DBCC CHECKDB DBCC CHECKDB('mydb') WITH ALL_ERRORMSGS; ```

  1. 1.**Try an older backup if available":
  2. 2.```sql
  3. 3.-- List all backup files and their dates
  4. 4.RESTORE HEADERONLY FROM DISK = N'D:\Backups\mydb_full_20260408.bak';
  5. 5.RESTORE HEADERONLY FROM DISK = N'D:\Backups\mydb_full_20260407.bak';

-- Restore from the most recent valid backup RESTORE DATABASE mydb FROM DISK = N'D:\Backups\mydb_full_20260407.bak' WITH NORECOVERY;

RESTORE LOG mydb FROM DISK = N'D:\Backups\mydb_log_20260408.trn' WITH RECOVERY; ```

  1. 1.**Check the backup source for disk errors":
  2. 2.```sql
  3. 3.-- On the source server, check for allocation errors
  4. 4.DBCC CHECKDB('mydb') WITH PHYSICAL_ONLY;

-- Check the source disk health -- Windows Event Viewer -> System -> disk errors ```

Prevention - Always use `WITH CHECKSUM` when creating backups - Run `RESTORE VERIFYONLY WITH CHECKSUM` after backup creation - Store backups on reliable storage with redundancy (RAID, geo-replication) - Keep multiple backup generations (daily for 7 days, weekly for 4 weeks) - Verify backup integrity before deleting old backups - Test full restore procedures monthly - Use `COMPRESSION` with `CHECKSUM` to reduce storage and verify integrity - Implement backup file hash verification after transfer