Optimize SQL Backup
January 13, 2015
Database backup is one of the regular tasks DBA’s perform but few explore all the options that are built in the product. Instead of exploring and using built in switches I noticed DBA’s try writing custom scripts or buying third party tools.
I explored some of the options and was able to do a full backup of 8TB size database under 2.5 hours. I obtained a 30% performance improvement with using non default values for 3 of the switches that come with Backup command.
- MAXTRANSFERSIZE
- BLOCKSIZE
- Number of BACKUP DEVICES (You can use up to 64)-These files can be in the same LUN under one folder. Depending on you LUN configuration you might chose to spread these into multiple LUNs to get better write throughput.
I do not want you to focus on the size of my test database or what was duration of different tests. Those are for information purpose only. I want you to focus on the percentage of improvement. You can implement this without any extra cost on hardware or software and gain about 30% performance improvement.
2 replies on “Optimize SQL Backup”
Thanks for the article. Can you tell us some more details about the server you were using? Number of Cores, memory?
Thanks!
Hi Victor,
Thanks for your comment. I tested this is in a VM and I really do not want anybody to focus on the size of the database as well as hardware, rather look the percentage of gain. As you asked it was a 8 cpu machine, with 64 GB or RAM, 58 GB was assigned to SQL Server engine.