Database growth causing issues sending backup over network

Question

We take a full database backup on our primary SQL Server, and send that file over our network to a remote site where it is recovered on a backup SQL Server. We use this backup database for slow queries that don't need the latest data, and, as a backup in the event of a complete failure of the primary database.

As the file grows it is causing problems with the available bandwidth. When the file transfer takes place, it sucks down just about all the bandwidth, which is OK. However as the file size grows (10+ gig) the amount of time that this transfer takes is becoming a problem because it is interfering with other things.

Is there some other strategy we can use to send our backup to a remote server?

Answer 1

There is no need to ship over the entire database. In fact SQL Server has a built-in solution for exactly this scenario, namely log-shipping. With log-shipping you only need to send the full backup once and subsequently you send over the log backups. Log backups will only contain changes from the last log backup and thus be usually quite small. The stand-by database will be read-only, perfect for reporting and queries. The article linked contains a link at the end to a step-by-step guide how to set it up.

Answer 2

Having identified bandwidth as your issue, the only options you have apart from bigger network pipes would be to use some sort of compression or data de-duplication method.

SQL Server (08 vers) supports backup output compression: http://technet.microsoft.com/en-us/library/bb964719.aspx

De-dup tech example: http://www.emc.com/backup-and-recovery/avamar/avamar.htm

Answer 3

You might look in this direction: Instead of compressing your backup and copying the whole thing over, you might:

1. Do an uncompressed full backup to a local drive and copy that .bak file to the remote server. (This will take a while the first time.)
2. Do daily full backups to the SAME .bak file and use an RSYNC-based file transfer agent to synchonize the local .bak file with the remote .bak file a. Something like Unison? http://www.cis.upenn.edu/~bcpierce/unison/
3. After the files are synch'ed then restore the remote db from the remote .bak file.

I've never tested it, but it seems possible that it may result in only propagating the "changes" over the pipe instead of re-transmitting the whole db every day.

Answer 4

The best solution for your case is using log shipping functionality. To setup it easily I would recommend you to use some third party program like EMS SQL Backup. It allows setup log shipping process by schedule and compress the sent backups which will decrease your bandwidth load even more.