Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 28, 2002

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

By Michael Aubert


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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM