I was recently tasked with engineering a new backup solution for our database server. I also needed to come up with a disaster recovery plan for the server. Due to limited funds, I wanted one solution to solve both tasks. Here is what I came up with

My server has 3 partitions, one for the operating system, one for log files, and one for data files. Connected to the server is a Maxtor 1 TB USB hard drive configured for RAID 1. This gives you 500GB of storage space.

I used Norton Ghost version 8 running off a Bart PE CD to image the operating system partition. Bart PE can be found here: http://www.nu2.nu/pebuilder/. Running Ghost off Bart PE is better for USB devices since Bart PE supports most USB drives without having to mess with a USB dos boot

disk.

Restoring the server via ghost is simple and fast. It takes less than 15 minutes to restore the server to a known good state from the image. This makes a catastrophic operating system failure almost painless.

Next I needed to solve the back up problem. I used a slightly modified version of the script found here:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=90 to do the backup. I defaulted the drive letter to my USB drive. I also modified the script to do incremental and differential backups.

Then I scheduled a job to run the full backup SP every night. I also run Differential backups every 4 hours and incremental backups every hour. All of these backup go directly to the USB drive. Personally I prefer to

clean up the old backups my self. When you have 500GB of storage space, the drive doesn’t fill up very fast. I use windows explorer search function to find all files older than 1 month on the USB drive (except the ghost image) and delete them.

For long term storage, I burn a full backup to DVD once a month.

Pros and cons:

Cost – This system only cost $700 for the drive. Tape solutions would be 5 to 10 time as much. Both systems would require extra software for disaster recovery.

Performance – USB 2.0 can sustain 33Mb/s throughput. SDLT is a bit faster at 36Mb/s. SDLT can support compression.

Reliability – The Maxtor USB drive in RAID 1 should last 3 years without any problems. If a drive fails, there is redundancy. Tape drives require cleaning and tape changing. There are more moving parts and more possibility for failure. Tapes can also break, get lost or become unreadable.

Database size – This solution would only work well on smaller databases. If you had a 100GB database, you would only be able to store 5 copies on the hard drive.

Limited space – While 500Gb is a lot of room, if you needed more space you would have to change the RAID level to 0. Personally I wouldn’t use RAID 0 for a backup solution. With a tape solution, you can always buy more tapes.

Kevin Parks parksk@eucom.mil is a Microsoft Certified Systems Engineer working for Headquarters United States European Command in Stuttgart Germany.