Administration

Can I speed up my backups?

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:

http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s