I’m back to studying for the 70-432. That means it’s time to cover another topic you’ll need to be familiar with in order to pass this exam. Today’s topic is Recovery Models. There are three recovery modelsavailable to you in SQL 2008.
- Simple
- Full
- Bulk Logged
To pass the 70-432, you’re going to need to be able to handle business needs using different options in SQL Server. So let’s talk through a couple scenarios and choose the recovery model that best fits the business needs.
Scenario 1
You’re asked to set up a new database to record web site metrics. This database will carry out hundreds of thousands of transactions per hour. If there is a failure, you need to be able to restore to a specific point in time.
The only way simple backup would do that is if you ran constant backups. With that kind of overhead… you simply aren’t going to get the ability to restore to any point in time, only the moment when the backup completed. But Full recovery will give you what you need.
Check out this BOL entry for details. With Full Recovery, you backup your transaction log (I’m currently using hourly transaction log backups in production).
When you go to restore, you’ll have an option just below the database selection to choose the specific point in time you want to restore to. You may want to read up on tail-log backups. Be careful implementing them, they can be tricky at first.
The bulk logged recovery model doesn’t give you point in time restore. It’s optimized for high volume transactions. It get’s it’s speed gain mostly from not logging every single transaction, but logging them in bulk. In exchange for performance, you give up the granularity in your restores.
Scenario the Second
You’re building a database that will be used primarily to stage data from your ETL processes. You don’t really care about the data that’s in the database as much as the database objects inside it. What recovery model do you choose?
Full? No way, that’s far too much overhead for this. Why would we want the ability to restore to a point in time? We only need to recover database objects in case of failure.
Bulk-logged? Well, it would be nice to only minimally log the transactions, since we will be using this database to simply stage the data before passing it along to it’s final resting place. But again, this is over kill.
Simple? Oh yeah. We only want to restore the objects, we don’t really care about the data in the objects. By setting a daily backup, we’re assured we get a picture of the objects as they were at the end of each day. Any changes to the database since the backup will have to be repeated.
Final Scenario
Let’s take the last scenario and change one requirement. We now want to be able to save the data since up to the last backup. Do you change the recovery model?
Yes! We now change to bulk-logged. Now, I’d also change the backup strategy to something closer to my full recovery model strategy. A full backup daily, and hourly transaction log backups. Worst case scenario we would lose up to an hour of data loads. We would have to re-run any ETL’s that came in within the last hour.
Summary
Knowing when to use each of the three recovery models will be on the 70-432 exams. If you’re having any troubles deciding when to use each of the three models, please let me know, I’m happy to help in any way! Maybe I can work with you to get more comfortable with these options, and grow a bit more confident in choosing between the options!