70-432:Another Backup Scenario

I’ve covered the differences in the recovery options before.  So you should be mostly prepared for the following scenario.  It requires you being familiar with the three recovery options, and knowing a bit more about the simple mode.


Scenario 1

You’ve got a database that’s used during business hours (0800 to 2000).  It’s currently set to Simple recovery mode.  You have no need to recover to a specific point in time.  You currently backup your database at midnight.

But, after a recent failure management has reconsidered how much data they’re willing to lose.  They now no longer want to lose an entire day.  They are willing to lose no more than 2 hours of data.

You’re not given any extra hard drive space for the backups, but you have to have to ensure they can meet the new business requirement, how do you accomplish the goal?


Solution 1

Add a database maintenance task that begins at 1000, and runs every two hours until 2000.  This new step will create a differential backup.

You’ll also need to create a maintenance plan to remove the full and differential files after each successful FULL backup.  That way you won’t use up all of your backup storage space.  If you need more coverage, you’ll need additional storage space.

Now if there is a failure at any point in the day, you’ll lose at most 2 hours of data.  While you won’t be able to recover to a specific point in time, you can recover the last full, plus any differential up to the point of failure!



To prepare for the 70-432 you need to be familiar with how to recover from failures.  One of the best ways to get used to that is to set up different backup strategies on your test server, then fail your database in different ways.  Only then will you know for sure what each of the recovery modes will give you.

If you have any questions, please send them in.  I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *