SQL Server Interview Question #193

How can you verify SQL Server backups?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

BACKUP ... WITH CHECKSUM can help detect certain I/O corruption during backup creation. RESTORE VERIFYONLY checks that SQL Server can read the backup set and that it appears structurally complete enough for restore processing.

However, VERIFYONLY is not a substitute for performing an actual restore. The strongest operational test is to restore backups to a separate environment and run integrity and application-level validation.

Code Example

RESTORE VERIFYONLY
FROM DISK = 'D:\SqlBackups\SalesDb_Full.bak'
WITH CHECKSUM;