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?
Best Answer
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.