I got this question the other day…it seems that my fellow DBA was finally tired of waiting for the backup to complete and wanted to know if she could speed it up. Let me look into it, I said…so I did.
I’m probably much like many of you…most of the time I setup a maintenance plan to back up the databases, or use SSMS to execute a special backup for whatever reason, or use SSMS to script the backup command for use in a SQL Agent job. Anyway, the point is, I NEVER had a need to look into all of the various optional parameters that can be used on a BACKUP command.
I found this article, which for me was a great example of backing up a large database and some of the different options available. So, thanks Henk VanderValk! Here is a link to the article:
Don’t be fooled by the title, it’s not all about compression and disks. There are a few nuggets of wisdom regarding some of the optional parameters that you can use to speed up your backups like backup up to multiple files and adjusting the buffer counts and transfer rates. Oh, and the article is for SQL Server 2008 R2, but I’m working with SQL 2014 now, and it’s still applicable.
I guess you can teach an old dog some new tricks.