Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 4 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
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? –  confused non-dba Feb 27 '12 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 –  Eric Higgins Feb 27 '12 at 18:04

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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