A Backup system is merely part of a recovery system. If your backups can’t be used to recover the database, then they’re useless. Do you regularly make sure that you can restore a database from your backups?
Verifying a backup is a useful practice. Verifying a backup checks that the backup is intact physically, to ensure that all the files in the backup are readable and can be restored, and that you can restore your backup in the event you need to use it.
The first thing you can do to ensure you have a good backup is to include CHECKSUM in the WITH clause of your backups, like this:
BACKUP DATABASE MyDatabase TO DISK = 'c:\bu\MyDatabase.bak' WITH INIT, CHECKSUM;
The CHECKSUM is a mathematical construct of a fixed length that gets generated from the page of data. The math will always arrive at the same number for the same page of data. The CHECKSUM value gets written with the page during the backup process and it validates that the page is intact prior to writing it to the backup. This is a good way to validate the media as you do the backup. Then, you can use the CHECKSUMs later during a RESTORE VERIFYONLY operation to validate the backup media is intact.
This does come with additional cost. It adds overhead, so if you have a very large backup that currently takes a long time, it’ll take longer. But, if you have a very large backup that takes a long time, wouldn’t you want to know that the pages being written to disk are actually intact?
Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this:
RESTORE VERIFYONLY FROM DISK = 'c:\bu\MyDatabase.bak';
VERIFYONLY will process the backup and perform several checks. First, it can find and read the backup file. Believe it or not, that’s a good first step. It also walks through the CHECKSUM information if you used CHECKSUM in the backup. This will validate that the backup media is in place. That can be a costly operation if the backup file is very large.
In SQL Server 2005 and later versions, the checks performed by RESTORE VERIFYONLY include:
• That the backup set is complete and all volumes are readable.
• Page ID (as if it were about to write the data)
• Checksum (if present on the media)
• Checking for sufficient space on destination devices
Finally, VERIFYONLY checks some of the header information in the backup. It doesn’t check all the header information, so it’s still possible for a backup to pass VERIFYONLY but still not restore successfully to the server. Which brings up the best way to validate your backups, RESTORE it!
The best way to know that your backup is intact is to run a RESTORE. It’s the only way to be sure. If you successfully run a RESTORE of a backup, then you know that backup is intact. By the way, notice that all these checks are only concerned with the backup structure and the database structure.
Transact-SQL has a set of DBCC statements used to verify the integrity of a database. Running a RESTORE and then running CHECKDB will enable you to ensure that your backup is intact and that the data in the backup is intact as well.