SQL Server 2000 Administration in 15 Minutes a Week: Creating a Disaster Recovery Plan (Part 1)

Friday Jun 21st 2002 by Michael Aubert
Share:

Michael Aubert's eighth article in the SQL Server 2000 Administration in 15 Minutes a Week series quickly reviews database recovery models before launching into an in-depth discussion on how to prepare a disaster recovery plan.


Welcome to the eighth article in my series SQL Server Administration in 15 Minutes a Week. A few weeks back we took our first look at Database backups. This week we will quickly review the recovery models before discussing how to prepare a disaster recovery plan. The topics for this week include:

- Recovery Models Review
- Creating a Disaster Recovery Plan


Recovery Models Review

Something you will start to notice as we get further into SQL Server is that most settings and options tend to be more of a balancing act than they are right or wrong. In other words, you will need to weigh the benefits and drawbacks of using one option over another. A good example of this would be the recovery models that we looked at in the last article. Some models allow for fast backup and use less disk space, but they don't provide the recovery options of other recovery models. Because this is an important part of your backup strategy, I would like to do a quick review of the three recovery models available with SQL Server 2000.

Simple

  • Uses full and differential backups
  • Recommended for development only, not for production databases
  • Bulk copy operations are fast and do not require large amounts of log space
  • Once log entries are no longer needed for recovery (after a checkpoint), log file space can be reused to keep log files small
  • If a failure occurs you can recover up to the point of the last full or differential backup, all data after the last full or differential backup will have to be redone

Full

  • Uses full, differential, and log backups
  • Recommended for production databases
  • Bulk copy operations must be logged row by row resulting in slower bulk operations and the requirement of more log file space
  • Log file space can't be reclaimed until the log file is backed up
  • Transaction log backups 
  • If a failure occurs you can recover to any point in time
  • If a data file is lost or damaged, no work is lost

Bulk_Logged

  • Uses full, differential, and log backups
  • Recommended for production databases when you need to perform many bulk operations
  • Bulk copy operations are not logged row by row resulting in faster bulk operations and lower log file space requirements
  • Log file space can't be reclaimed until the log file is backed up
  • If a failure occurs and no bulk operations have occurred since the last full/differential backup, you can recover to any point in time of a log backup. If bulk operations have occurred, you can recover to the end of any backup.


Page 2: Creating a Disaster Recovery Plan


 » See All Articles by Columnist Michael Aubert


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


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved