A few months ago, we were called into a company facing a nightmare scenario: their primary database became corrupt during a routine update. They had a solid backup strategy in place—daily full backups, and transaction log backups every hour. Confident that restoring the database would be a straightforward process, they initiated the restore process, only to discover that their backups were corrupted as well. When they called us they had been offline for 2 days. This story is not about how we recovered their data. That was messy and took quite some time, this story is about how they could have avoided being in that situation in the first place.

An often neglected aspect of a database backup strategy is verifying backups. Simply taking regular backups and assume they’ll work when you need them is fine – until they don’t. Verifying your backups are intact and can be restored can be just as crucial to your disaster recovery plan.

Verifying backups ensures that the backup files are free from corruption and contain all necessary data for a full restore. SQL Server provides several tools to check the integrity of your backups and give you confidence that, should disaster strike, you can recover your system quickly and completely.

The simplest way to verify the integrity of a backup file is by using the RESTORE VERIFYONLY command. This checks that the backup file can be read and is not corrupted. Here’s a simple example:

RESTORE VERIFYONLY  
FROM DISK = 'C:\Backup\MyDatabase.bak';  

This command performs a basic integrity check without actually restoring the data. It ensures that the backup is valid and ready for use. For a more thorough validation, you can restore the backup to a test environment. Performing a restore on a non-production server ensures that all data and objects are recoverable.

RESTORE DATABASE MyDatabaseTest  
FROM DISK = 'C:\Backup\MyDatabase.bak'  
WITH REPLACE, NORECOVERY;  

By doing this in a test environment, you ensure that your recovery process will work smoothly during an actual disaster recovery situation. It also gives you a fairly accurate idea of how long it will take to restore a database. Too many times the Recovery Time Objective(RTO) is ‘as quick as possible’. Let’s be honest – that’s probably still going to be the RTO, but at least if you are doing semi-regular restores of those backups you’ll know that they are both working and you’ll be able to give a non-guessed answer of how long it would take to recover if needed.