1

I have moved the location of sql server backups\restores to a file server on the network at an offsite data center. Unfortunately now my backups/restores for the local reporting server is causing overages to the average bandwidth we pay for from the data center provider per month. Is there a way in sql server to limit bandwidth used for restores/backups?

For clarification, the offsite Data Center is the Production environment for the DB's and I am restoring db's to a local db environment for internal reporting. DB backups are also being copied to an alternate location for D/R purposes. The new part in the equation is the restores across the network for internal reporting. That's what pushed me over my bandwidth limit.

3

If backups/restores are the only activities occurring between the SQL Server and your offsite Data Center, you could setup Firewall Quality of Service rules to limit transfer speeds. The total amount of data you transfer can only be reduced by lowering the amount of data you send (such as only sending every other backup).

I will highly suggest that you adjust where your backups are created, though. You should backup your databases locally (preferably to another server within your local data center or to a separate backup drive locally on the server itself if you don't have other options available), and then configure a job to copy the backups (or every other backup) off site for long-term disaster recovery purposes. Not only could this help reduce your bandwidth issue, but more importantly, having at least one backup locally will eliminate the possibility of a network outage or some other external factor from preventing you from recovering the database in the event of an emergency. Backing up a database directly to an external data center is a risky proposition as you can generally count on some sort of network issue every now and again. I've seen far more network related outages than disk or server outages, though my experience may not be reflective of everyone's.

| improve this answer | |
  • Are you advising backing up the database to the same server that the database is on? That's a recipe for losing backups and not being able to recover at all. – Sean Gallardy - Retired User Sep 13 '17 at 22:38
  • 1
    @SeanGallardy I'm not recommending only backing up locally; my answer does state copying backups off site should also occur. Really the setup will depend on the environment available, but if the choice came down to either backing up to an external DC or to a local drive, I'll take the local drive every time. I've seen far more network outages than server failures. Hopefully the updates I made clarify what I mean, but great question and hopefully I don't sound quite as reckless now. – John Eisbrener Sep 13 '17 at 23:57
  • 1
    So from your answer, and others I got directly on twitter, it looks like my only option is to set firewall rules to limit the transfer speeds. I thought that was the case, but wanted to make sure. Thanks! – William Salzman Sep 14 '17 at 12:32
0

You could try using these settings with the RESTORE command:

, MAXTRANSFERSIZE = 65536
, BUFFERCOUNT = 4

Fuller usage example:

enter image description here

| improve this answer | |

Your Answer

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

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