Database Recovery: Day 7

Recently we were talking about Backups and how you need to test them often. The most complete way to test a backup is to restore from it. The syntax is already documented out there. Also don't forget that our contest giveaway is tomorrow! All you need is a single comment on this post containing an E-Mail address!

What plan to use?

This relates more to the backups themselves, not the restores... but it all factors in. Do you only take full backups on Monday at 0100? Do you have it in Full mode? If so, how many files do you get to restore when it fails Sunday at 1900? Don't get caught in that. Something to consider would be running differentials.My personal rule is to only use differentials if the full backup is over 3x the size.once it reaches 50% or higher, that adds a lot of time to your restore.I like having full backups on Fridays right after main business hours, Differentials taken every day at that same time and on Saturday or Sunday, script out a full restore to a secondary server. You'll have your backups running daily, tested every week, and all of it will be automated. You can just check for that nasty E-Mail saying something failed.

Secondary Server

You can restore your backups to a secondary server. Good targets for this are both your development and test servers.If you have one, ask your developers if they have any issues with weekly or even monthly restores of current data. It may be a great way to help them work on what you all are currently pushing out. De-identification of data may be required, keep that in mind.


Do you not have a spare server laying around? Ask for enough storage on your san or if it fits, even an external drive and restore it to something like VMWorkstation. Something is better than nothing here. If your backups cannot fit on a 3 TB external, You're probably at the size that helps you push to get a spare server for testing backups. If they don't want a whole server for just backups, offer its use for your developers if they don't have a server currently.

How Often?

I prefer to test weekly if possible, monthly if you have too. The real question is, how much is your data worth to you? Can you lose a month of data and noone care? If so, do it every other week or so. Remember the SLA's you may have in place. If you have to be able to restore 24/7, testing your backups is the only way to be sure.

No comments:

Post a Comment

All opinions welcome! Please comment with any changes thoughts or discussion points.