Best Option for Backing Up DB to Offsite Server 1

[JohnBates]

hi all,

This is SS 2000 Std.

The db is 28 GB in size.

I've been asked to come up with options for backing-up this db to a server at another location - for disaster recovery purposes. This db is used by a website. We only need daily "backups" - the data att he alternate location can be as much as 24 hours old. The offsite SQL Server 2000 server will be on our network via a VPN connection (speed of 4.5 MB per second I've been told)

I suppose some options are:
1. backup to the offsite server
2. -or- copy the .bak file to the offsite server
3. snapshot or merge replication (yuk!) remember this is 28 gigabytes
4. overnight delivery of a tape containing backups of master, msdb and the 28GB user db

5. ???

Any thoughts?

Thanks very much. John

 

[mrdenny]

I would use a custom built log shipping solution for this. Basically the log shipping from my FAQ faq962-5754 but with a twist.

Do a full backup and restore to the offside location.

Once the full backup is done and you have started the file transfer across the WAN begin doing transaction log backups. Instead of backing up to a static file name each time, backup to a dynamic file name (database_name_YYYYMMDDHHMMSS.trn). As soon as the file has been backed up, copy the file to the remote server. Then instead of starting the restore right away, via a sp_start_job, I'd simply schedule the job to pull in the list of files in the folder and restore all the log files in order once or twice a day.

This will give you your restore requirments with the ability to have more data if needed. It will keep the transfer time low (moving a file that size over a 4.5 MB conenction will take several hours at least if not a day or to).

I use a setup much like the one I've just described for a reporting database for a database which is 140 Gigs in size. When I started at the company there were doing a full backup daily and copying the file to another server. Over a 100 Meg LAN connection the file copy would take up to 5 or 6 hours on a bad day. (Keep in mind that you won't be able to have the entire 4.5MB WAN connection to your self, other processes will probably be needing bandwidth as well.)

Failing this, with the network speed you've stated about the only option that you will have it to ship the tapes to the remote site which will get very expensive, very fast.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com