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.
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.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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.