Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Back Up Your Database With USB

By Kevin Parks,

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.

Total article views: 8700 | Views in the last 30 days: 3
 
Related Articles
FORUM

cluter server databases backup on local drive

cluter server databases backup on local drive

FORUM

Error-Backup on network drive

SQL server 2005 backup on network drive

FORUM

Backup Solutions

The best way to backup multiple servers (not just SQL!)

FORUM

Error: Copy backup to external drive

Error: Copy backup to external drive

SCRIPT

SQL Server System Report

Overview report for tracking key database performance metrics, general system stats, overall activit...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones