Mistake #81: treating a successful backup job as proof that restoration will work. Completion checks show that a job finished; integrity checks show that the backup remains internally valid and usable. Testing every backup may be impractical, but skipping verification can leave failures undiscovered until recovery is urgent. section 12.4 section 12.4.2
Use a layered check instead of relying on one successful job status:
- Confirm that each backup job completed successfully; do not ignore reported failures. section 12.4
- Enable page checksums so SQL Server can detect mismatches when pages are read. section 12.4.2
- Take backups with WITH CHECKSUM to generate a database-wide checksum, then run RESTORE VERIFYONLY WITH CHECKSUM. section 12.4.2
- Add verification after native backup jobs; for enterprise tools, coordinate a scripted API-based approach with the backup team. section 12.4.2
A practical Marketing database check
For the Marketing database, turn on page verification, create a checksum-protected backup, and verify the resulting file. This raises confidence without requiring a full restore of every backup. section 12.4.2
Listing 12.3 Turning on page checksums
ALTER DATABASE Marketing SET PAGE_VERIFY CHECKSUM ;
Shows the ALTER DATABASE command for enabling page checksums on Marketing. section 12.4.2
Listing 12.4 Backing up a database with checksum
BACKUP DATABASE Marketing
TO DISK = 'D:\Backup\MarketingFull27122023.bak'
WITH
NAME = 'Marketing-Full Database Backup'
, CHECKSUM ;
Shows a SQL Server backup using the CHECKSUM clause. section 12.4.2
Listing 12.5 Verifying a backup
RESTORE VERIFYONLY FROM DISK = 'D:\Backup\MarketingFull27122023.bak' WITH CHECKSUM ;
Shows RESTORE VERIFYONLY validating the checksum of the backup file. section 12.4.2
