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 data is changed, and are we willing to lose any work for an improvement in speed? Lets look at a scenario.
In this scenario the database we are creating a back up plan for is use 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 to 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 last type of backup 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 take a whole lot of resources to make, so we can perform them without impacting the system to 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 at 12:00 AM FULL BACKUP
MON THROUGH SAT 2:00 AM DIFFERENTIAL BACKUP
This plan is not perfect — it has it’s 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 fine that a loss of 30 minutes worth of data (the time between transaction log backups) is to much — it all depends on what your 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 no matter if 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…not a whole lot. The next article in this series outlines some steps to improve the plan.