asked
How do I get my backups to finish being created faster?
Currently I'm working with Sql server 2012, Enterprise edition SP1. We have one database that is about 500GB in size. The backup for this database takes anywhere between 2-3 hours to be created. The backup is compressed to about 110GB. I know that having the backup run on a faster disk and having fast bandwidth plays a role in this as well, but regardless of that information, is there anything on the SQL side of things that can make a backup of this size get compiled and finished being created faster, even by an hour or so?
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
You already mentioned the basics of I/O, but there is another related feature that might help a lot. SQL Server supports backing up to multiple files. I could go on to explain more about that, but I'll let Jes Borland handle that for me. :) In this article, Jes first covers using different backup types like full and differential. Then her second topic is backing up to multiple files. Take it away, Jen ... [
http://www.brentozar.com/archive/2014/01/improving-the-performance-of-backups/][1] [1]:
http://www.brentozar.com/archive/2014/01/improving-the-performance-of-backups/
Since you already have compression, there are only a few options open to you. First, you can stream the backup to multiple locations, multiple files, allowing more than one disk to help with the I/O. You can also adjust the [BUFFERCOUNT and MAXTRANSFERSIZE][1] to see if that will help your backups. Microsoft has a [few suggestions here][2]. I wrote an [article on RESTORE][3] that is largely applicable to BACKUP as well. [1]:
https://msdn.microsoft.com/en-us/library/ms186865.aspx [2]:
https://technet.microsoft.com/en-us/library/ms190954(v=sql.105).aspx [3]:
https://www.simple-talk.com/sql/backup-and-recovery/faster-restores-best-practices-to-increase-speed/
19 People are following this question.
