SQL Server 2000 Administration in 15 Minutes a Week: Creating a Disaster Recovery Plan (Part 2)
June 28, 2002
- Creating a Disaster Recovery Plan - Continuation
Last week we decided on how often to backup our database; now we must decide where to backup our database. The first option available is to backup directly to tape from SQL Server. On the plus side, a backup to tape allows for off-site storage of backups. On the down side, tape backups are slow and therefore can impact the server for a longer amount of time than a backup to a file. The other common place to backup to is a file. File backups are much faster than a tape backup, for both backup and restore operations; however, backups to files don't allow for quick off-site storage (unless you happen to have a high speed remote link).
A third option is to use a combination of making backups to a file and then using another backup utility, such as NT Backup, to copy the file backups to tape. By making backups to files on another server nearby and then copying the files to tape, you can minimize the time a backup will impact your SQL Server while still allowing for off-site storage of tapes. Also, if you need to make multiple copies of tape backups, using another computer for copying backups to multiple tapes can help even more.
Another thing to think about when you are choosing where to make a backup is the time it takes to restore a backup. For example, a backup that is stored on another computer could be restored much faster over a high speed network than it could be from a tape drive. To take advantage of this faster recovery you may consider saving file backups for the week on another computer (in addition to your tape backups). In the event your SQL Server crashes you have a current backup on hand and available at a faster speed than tape.
File and tape backups do provide for lots of flexibility in designing your disaster recovery plan, but there are still many options available from third party venders. For example, you can find utilities that make the process of making backups of multiple SQL Servers very simple. You may also consider a Storage Area Network for large mission-critical systems. Although I would love to cover every option available, other backup utilities and hardware options are outside the scope of this series.
Before we can move on, there is
a second half to deciding where to backup...choosing a
tape, or file for that matter, rotation. If you used a new
tape for each day's backup you would probably eat up a good
part of your budget on nothing but tapes. In order to save
money a few popular tape rotation schemes are in use. The
rotation we are going to look at is known as the
Grandfather-Father-Son rotation. Let's look at our example from last
week to see how this rotation works (note: I have rearranged
the order of the days from the last article):
In a Grandfather-Father-Son rotation you start out by using a new tape for each day of the week for the first week. For each week following the first week you reuse the same tapes except for the last tape of the week. By using a new tape at the end of each week you can keep an archive of data. In the event you need to restore data that was deleted or lost, the archive from past weeks is available. Once a month has gone by you keep the tape for the last week of that month and then reuse the end-of-week tapes. Here is what a Grandfather-Father-Son rotation would look like for our example over a two month period:
11 Tapes are used: M, T, W, TH,
F, S, W1, W2, W3, Month1, Month2...
In our example we must take the monthly backup on Sunday because that is the only day we make a full backup. However, if you make a full backup of your database every day of the week you can use the monthly tape on the last day of the month no matter what day of the week it ends on. To illustrate, this is what a Grandfather-Father-Son rotation would look like if we took a full backup every night for the next two months:
Note that a new tape is substituted for the last day of each month. Also note that once the last day of the month has passed, the end-of-week tapes can then be reused.
May 2002, Wednesday 1st - Friday 31st:
June 2002, Saturday 1st - Sunday 30th: