Are Your Backups Useless?

SQL Server backups are useless if you can't recover them. Backups are simply big disk files unless you have a recovery mechanism that puts those bits back into SQL Server when you need them. So when was the last time you tested your restore strategy? I'm not asking whether you've performed a backup and run the RESTORE command manually to see whether the media is valid. I'm asking whether you've tested your restore methodology to make sure it works the way you think it does. Can you get your production database back up and running after a disaster? You must have a planned and tested restore methodology to be sure.

Two weeks ago at SQL Server Magazine Connections in Orlando, Florida, I sat in on Kimberly Tripp's talk about SQL Server backup and restore. Kimberly presented a number of interesting tips, but her fundamental backup tenet is that the backup is useless without the ability to restore it. And you don't know that you can restore your backup unless you've fully tested your plan.

I suspect that many of you don't have a well-tested backup and recovery plan. Testing backup and recovery plans can be difficult, especially if you don't have the hardware resources to do a complete dry run of a failure and recovery. For example, properly testing your restore methodology is hard to do if your production system is a one-tier warehouse and you don't have a test server of equal capacity. But budgeting for adequate testing and quality assurance equipment should be a non-negotiable part of an efficient data center. If you haven't planned how to recover your data and tested that plan, when a true disaster happens, you're asking for trouble.

If you haven't tested your backup and recovery plan, your backups might not be as valuable as you think they are. Backing up is easy; getting the data back can be the hard part.