Creating a Disaster Recovery Plan

There are a few steps in creating a disaster recovery plan. First, we need to decide how often we are going to back up our database. Some points to consider are: how much time do we have to make backups, how much (database size) do we need to back up, how long would we like it to take to restore a database, how often does data change, and are we willing to risk losing any work for an improvement in speed? Let's look at a scenario.

In this scenario the database we are creating a disaster recovery plan to be used by our company's sales department. The database contains customer order information. Any loss of this information would be devastating to the company. The database must give maximum performance during most hours of use. When looking at the performance logs, we can see that the server utilization is lower from 2-3 AM on Monday - Saturday and most of Sunday. When testing the time needed to make backups, you note a full backup takes 4 hours and a differential backup takes up to 30 minutes.

Before even looking at what options we have, notice that I list performance logs and the time needed to make the two types of backups. This is important information you will need to collect or estimate.

Because the data in this database is so important to the company, we need to back it up frequently and use the Full recovery model. A full backup takes 4 hours, so the only day we can do this (without impacting the system too much) is on Sunday. Now you don't have to use differential backups, but in this scenario they fit in well. We can make a differential backup Monday - Saturday mornings, reducing the time needed to recover from a failure.

The final backup type we have to consider are the log backups. Again, because the data in this database is so important to the company, we should perform log backups frequently throughout the day. Log backups normally don't require a whole lot of resources to make, so we can perform them without impacting the system too much. In this scenario we will back up the transaction log every 30 minutes.

Here is what the backup plan would look like without the log backups.
 

SUN MON TUE WED THU FRI SAT
12:00 AM FULL            
1:00 AM              
2:00 AM   DIF DIF DIF DIF  DIF DIF
3:00 AM              

This plan is not perfect -- it has its benefits and drawbacks. On the plus side, this strategy only makes full/differential backups when the server is at its lowest utilization. On the down side, if the database fails late in the day, we would have to restore a bunch of transaction log backups. Also, you may find that a loss of 30 minutes worth of data (the time between transaction log backups) is too much -- it all depends on what you're willing to risk.

Another thing you must keep in mind is that backup times will vary. Using our scenario as an example, because a differential backup will backup the data that has changed from the last full backup, Friday's differential backup probably has more changed data than Monday's differential backup. Why? If you change the same exact data over and over again, a differential backup should stay about the same size regardless of whether you take the backup today or two weeks from now. On the other hand, if you modify/add different data, the size of the differential backup will continue to grow until you take a full backup.

So, could we improve our backup strategy? With a full backup taking 4 hours and a lot of server resources...the answer is no, not a whole lot. In my next article we will look at some other options that will allow us to improve our plan, but for now we are limited by the time constraints.

Now that we have decided how often we are going to back up our database it is time to think about where we will store our backups...and that is exactly what we will talk about next week, in part two of this article. As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.

Mike
maubert@databasejournal.com
www.2000trainers.com


» See All Articles by Columnist Michael Aubert