We have 4 different sites and each site has a SQL Server.

Our requirement is to have a SQL Server backup or database (Mdf files) of site 1, 2, 3 in site 4.

In case of disaster on any of the site we need most recent database of the site.

each of the site is connected in network with very limited bandwidth. As the database size is more it is not possible to take complete backup every time.

For the above scenario can any one suggest me a better solution?

  • I think this question would be better suited to the more specific dba.stacexchange. Don't cross-post though, I'll vote to move it and if others agree it'll get moved automatically. Apr 3 '13 at 11:27

You can use Deltacopy http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp it's a rsync solution for Windows, it can be used to sync files or folder between servers.


There are a great many optoins here, the best ones to choose will depend greatly on your application and environments. At very least you need to specify what the bandwidth is between the sites, the sizes of the databases involved, and the amount of write activity they see each hour/week/day.

As you are using SQL2008r2, look into compressed backups - this may be enough to make transferring the whole file in each practical. I assume this uses zip-style compression in terms of the balance between CPU time and compression rates - for our large databases this reduces them to around a fifth of their uncompressed size. For better compression (but longer processing time) use an uncompressed backup then compress it as a second step using 7zip, we find this gets roughly twice the compression rate for our DB but definitely takes more then twice as long to process the same amount of data.

If your data does not change much then consider using something like rsync to transfer the backups as its delta transfer method (combined with the built-in compression option for data that does need to be sent) can be very efficient for large files that do not change a lot between transfers. Make sure you don't used compressed backups in this case though.

To reduce the amount of data in the backups, for any of the above options, consider moving your non-clustered indexes to a different filegroup and not including that in the backup (just the filegroup(s) with the clustered indexes and heaps). This will add an extra step (recreating all those non-clustered indexes) each time you need to restore a backup though.

Of course you could try setup log shipping to keep remote copies of the databases up-to-date, though this is not usually recommended over slow links.

You could also look into taking incremental backups instead of full backups, essentially creating your own difference shipping solution, but the extra complication here may not be worth it (and you would need to send a full backup over occasionally or you'll end up with hundreds of differentials over time, consuming space and making restores too cumbersome).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy