SQL Server Best Practices: Guard the Backup Files

You probably heard me say (along with a lot of other folks) that you need a good recovery strategy, not just a good backup strategy. The thought here is that you’re not backing up for it’s own sake, you’re taking the backup in case you need to restore data. Since that’s the ultimate goal, you should, as the golfers say, “play the shot all the way through”, or take your backup strategy to it’s final result – a good restoration.

As part of that strategy, you should make sure that you protect the backup files. That means backing the databases up to a secure drive other than where the databases are stored in the first place. This protects you against a single drive failing and taking out not only the data but the means to restore it.

If you back up to the same drive where the data is stored, just make sure that the next step in your automated process is to copy the backup somewhere else. Of course, if you back up to tape, you’ve avoided this problem.

But it doesn’t end there. Even if you back up to tape, you aren’t safe if you store that tape in the same building as the server. If the building has a flood or fire, once again your data and the means to restore it are in the same place.

So what you should do is back up your server, copy that to tape, and then send the tapes off-site to a secure location (there are services for this) so that you can recover no matter what happens. Oh, and document the server, tape software, and tape drive system so that you can replace that too if the building has a catastrophe.

And as always, make sure this is a documented process that your company knows, and that they know how long you think it would be to bring it all back from zero. And as always, it’s always a good idea to perform a “test run” on a recovery scenario. It can be a real eye-opener.

Skip to main content