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 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.

Thanks in Advance.

share|improve this question

migrated from serverfault.com Apr 3 '13 at 16:35

This question came from our site for professional system and network administrators.

    
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. –  David Spillett Apr 3 '13 at 11:27
add comment

2 Answers

There are a great many optinos 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 aronud 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 rought 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 insted of full backups, essentially creating your own difference shipping solution, but the extra comlpication 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 combersome).

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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