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



Welcome to the ninth
article in my SQL Server Administration in 15 Minutes a Week series. Last week we started to create a disaster
recovery plan. This week we are going to finish working
on our disaster recovery plan. The topics for this
week include:

– Creating a Disaster Recovery Plan
– Continuation


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):
 

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

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…
 

MON TUE WED THU FRI SAT SUN

Week 1

M T W TH F S W1
Week 2 M T W TH F S W2
Week 3 M T W TH F S W3
Week 4 M T W TH F S Month1

 

MON TUE WED THU FRI SAT SUN

Week 5

M T W TH F S W1
Week 6 M T W TH F S W2
Week 7 M T W TH F S W3
Week 8 M T W TH F S 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:

MON TUE WED THU FRI SAT SUN

Week 1

    W TH F S W1
Week 2 M T W TH F S W2
Week 3 M T W TH F S W3
Week 4 M T W TH F S W4
Week 5 M T W TH Month1    

June 2002, Saturday 1st –
Sunday 30th:

MON TUE WED THU FRI SAT SUN

Week 1

          S W1
Week 2 M T W TH F S W2
Week 3 M T W TH F S W3
Week 4 M T W TH F S W4
Week 5 M T W TH F S Month2

Confused yet? 🙂

Page 2: Disaster Recovery Plan (Continued)

 »


See All Articles by Columnist
Michael Aubert

Latest Articles