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?

I know that you’re probably running regular database backups. Most people do. Some don’t, but that’s a different discussion. There’s an old saying “Your data is only as good as your last backup.” That’s very true. But, there’s a little known corollary to this: “Your backups are only as good as your last restore.” It’s great that you’re backing up your databases, but you need to do more. You need to test your backups.

The ultimate test for any backup is a restore to a server, but there are a few other things you can do as well. Let’s go over them in the order of their importance in validating that the backups you have are good.

CHECKSUM

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:

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 (see below) 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?

VERIFYONLY

Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this:

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. Far too many people will assume that they can restore a file that is either incomplete or inaccessible. It also walks through the CHECKSUM information if you used CHECKSUM in the backup (see above). This will validate that the backup media is in place. That can be a costly operation if the backup file is very large, so I don’t know that I’d run this check from my production system if I could help it. 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.

RESTORE

Like I said at the beginning of this, the best way to know that your backup is intact is to run a RESTORE. It’s very much like taking off and nuking the site from orbit, it’s the only way to be sure. If you successfully run a RESTORE of a backup, then you know that backup is intact. Yes, other things might happen to the file later, but for a moment in time, you’ve validated that your backup and storage mechanisms are working. By the way, notice that all these checks are only concerned with the backup structure and the database structure. What if what you’re backing up is junk?

CHECKDB

You might think that running CHECKDB should be done first, prior to running a backup. And, in most circumstances, you’d be right. However, it is possible to find yourself in a situation where DBCC CHECKDB is extremely expensive. You might not have a big enough maintenance window to run the CHECKDB and get a backup completed. In that case, run the backup, it’s the more important operation. But, you can still validate your database. Because the backup is an exact copy of the database, if there are corruption issues (not found by the CHECKSUM) they’ll be backed up as well. 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.

Summary

You know that you need to protect the information for your organization. You’re already running your backups. Now take the next step and ensure that those backups are good. Test them. Just remember, that the very best test is a complete restore.