At its core, backup speed boils down to just two numbers.
Question 1: How fast can SQL Server read data from disk?
You can measure this by doing a full backup to disk, but use the NUL: option:
BACKUP DATABASE MyDb TO DISK='NUL:'
This does a backup, but doesn’t actually write it anywhere, so it’s a measure of how fast the data files can be read from storage. If you run this, know that you’re taking a real backup: this backup can break your differential backups.
Question 2: How fast can SQL Server write to the backup target?
You can measure this using disk benchmark tools like CrystalDiskMark or Diskspd. If you’re using a UNC path for your backup target, you may need to temporarily map a drive to get these tools to work. If you’re the only server/user who does backups to that location, focus on the sequential write speed numbers. If multiple servers/users will be doing backups at the same time, focus on the random write speed numbers.
Your backup speed is determined by the bottleneck – either question 1’s answer, or question 2’s.
How to Read Data from Disk Faster
Make the drives faster. Tune your storage. Ah, I wish it was as easy as typing that three-word sentence.
Make it smaller. Use Enterprise Edition’s data compression to shrink the size of your database on disk. Archive tables and indexes that you’re no longer using.
Read it from somewhere else. If you’re using AlwaysOn Availability Groups, consider doing your backups from the fastest replica available.
Back up less data. If you’re doing daily full backups, you can switch to weekly full backups instead, and differentials every day. Just know that the differential by itself is useless – you need to make sure you have both the full and the differential available to do a restore to the most recent point in time.
Back up more often. If you’re only doing log backups once an hour, try nibbling off the logs in smaller increments instead.
How to Write Data to Disk Faster
Use backup compression. This is included free with modern versions of SQL Server, and it gets even better with third party backup tools that often have tweakable levels of compression. Experiment with different settings to find the right compression setting for your biggest databases. It’s a balance of how much CPU the tool uses, versus how much smaller the file output becomes. If your bottleneck is slow writes, and you have lots of idle CPU time, then the tradeoff makes sense.
Write to a faster target. While SATA RAID 5 gives you lots of capacity for backups, it may not be fast enough to get the job done.
Don’t involve third party appliances. If you’re using a dedupe appliance like a Data Domain, it may not be fast enough to keep up with SQL Server’s reads. Try doing a temporary backup to a plain ol’ file share, and compare the backup speeds. Then work with your sysadmins to see if there’s a way to tune your backup appliance.
Team multiple network cards together. If you’re using 1Gb Ethernet and backing up to a network share, try teaming multiple 1Gb cards together on both the SQL Server and on the backup target on the other side.
Use multiple backup files. Try striping the backup to multiple files. If your backup target is fast enough to keep up, writing to 4 files can often improve your backup throughput by 4x.
And when you’re all done tuning backups, it’s time to tune restores. After all, a really fast backup doesn’t matter when the system is down – the business is more concerned about how long the restore will take.