Optimize SQL Backup

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.

 BackupImprovement

FacebookTwitterGoogle+Share