1

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?

4 Answers 4

4

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.

1

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

2
  • we do compress the file before sending it down. the problem is that for X continueous hours the backup utilizes the entire bandwith. is there any way to spread out that transfer? possibly not sending a full backup, but smaller things as the change? Feb 27, 2012 at 16:49
  • My understanding is you are sending full backups to another location for safe keeping. If you are looking at more of a high availability option, Database mirroring could be a great option for you. Once initialized it will not require full backups to be sent. Another option for you might be log shipping Feb 27, 2012 at 18:04
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.