This past Saturday was World Backup Day, It’s not an actual government recognized holiday, but as a DBA you should start celebrating it. Maybe celebrate isn’t the right word for what you might want to do on WBD. If you have a copy of the SQL PASS Summit 2011 DVDs, check out Grant Fritchey’s Lightning talk rant: Backup Testing. It’s a quick 5 minute rant on some of the things you’re going to want to do to celebrate the day.
Backup databases you don’t want to lose
It’s as simple as that. Pick the restore strategy that matches how much data you’re willing to lose. If you’re ok only getting back to a specific point you made your backup, choose simple. If you want a little more control, and want to be able to recover to a point in time, choose full. If you want to get really fancy, and set up faster backups and restores, let’s talk. We can get really fancy with these backups.
Now that you have your backups…
Store them some place safe
Yeah, you’re probably backing up to a disc drive or attached to your server, right? That’s alright to use to create the backups, but you need to get them off the machine used to create the backups. Your goal in creating backups has to be to protect against failure. Are you protected from a machine failure if your backups are stored on the machine you’re trying to protect?
The two ways I protect my backups is shipping the backups off to another machine on my network, or shipping them off to a cloud storage solution. The cloud solution will cost you based on the amount of storage you use, but if you were to lose the data center that houses your SQL server and the backup server, you will still lose your backups. That’s why I like the cloud solution. Your backup files are generally stored on several servers, rather than just one.
You do have the trade-off of restore time. You’ll have to download those files from the cloud service provider in order to restore them. You’ll have to consider the costs when deciding where to store your backups.
Test your backups
Like Grant mentioned in his Lightning rant: You can apply all sorts of settings to your backups to make sure they’re written properly, but the only way you can be sure your backups are successful is to test them. The best way to do that is to run a restore of your backups to another server, and make sure the databases come back online with all the right data on the other server.
So, let’s make world backup day, world backup testing day too. Pull your latest backups and try to restore them. That way, if any fail to restore, you’ve got a chance to fix the problem before you need those backups.
It could mean the difference between being able to recover from a failure, and not recovering from that failure.
If you need help, or have any questions about planning for disaster recovery, or testing your backup strategy, let me know! I’m here to help!