- People do not know how to tune their backups.
- It takes too long to run through the tuning process.
How to tune your backups
I’m not going to go over methods for tuning your backups in this post. There are people who have done a far better job at both explaining the adjustments you can make and that have created videos to help you understand and perform the process yourself.
My biggest concern was directed at the level of effort required to test all the possible permutations of files, maxtransfersize and buffercount values, after all, who has time to figure all of that out and then capture the data to look at the relative performance characteristics of each one?
I decided that the best way to do this was to create a nice little test harness which would run through all those tests without manual intervention, and then figure out a way to get the output from all of those backups into some kind of meaningful graph for that instant visual on performance differences.
The backup testing script
Step one in the automated tuning is a SQL script I created which accepts a few input variables:
- @DatabaseName – name of the database you want to use for testing
- @MaxBackupFiles – maximum number of files you want to write to at a time
- @BackupFolder – destination folder for the backups
- @WithCompression – whether or not to use SQL Server backup compression
- @CreateJob – Whether or not to create a SQL Agent job to run the tests
- @JobLogFileName – file path and name for a log for the job
- @OutputToScreen – outputs the backup commands to the screen
When executed the script is designed to created SQL Agent job which you can then execute at a time of your choosing. The job will run through a series of backups for a database (at minimum 40) and capture the information in the job log file.
Continue reading on SirSQL.net.