Parallel databases backup in SQL server

On By ADsqlIn Uncategorized
Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Infrastructure: This has been run and tested successfully on SQL 2005 and onward.

Scenario: Parallel backup as the name itself suggest at a time multiple databases backup could be executed on SQL server. When we run the databases backup or configure databases backup to run through maintenance plan, its run sequentially i.e. one by one.

But assume you wanna run multiple databases backup parallel automatically with the help of scripts.  Parallel databases backup is possible manually but its tricky to run it automatically. So, here I will discuss about how to achieve this.

First of all, it is not recommended to run databases backup parallel as this costs IO but in some cases it is quite helpful.

This could be possible during low load.

Resolution:

This could be achieved with the help of Maintenance plan and SQL scripts which I have shared below.

Follow the steps to get this done.

  1. Create a maintenance Plan and add t-SQL execute task from tool bar in design area with same level as below and put the code from here.
  2. The no of t-sql execute task you added in design area, the no of databases will be running parallels from each task.
  3. Example, assume you have total 10 user databases for which you looking for parallel DBs backup. Out of 10 DBs you can put 2 databases in first t-sql task and 2 in second t-sql task and 6 in 3rd t-sql task based on their sizes.
  4. This way you can see 3 databases at time will be running parallel from each task. Hope this make sense.
  5. You will have to put parallel DBs backup code in each t-sql task while keeping small changes as per requirement and above.
  6. One more thing to notice, we should add (optional) wait For Delay clause from 2nd t-sql task till last t-sql task and keep delay by 2 mints at least just to avoid sudden load on IO due to running multiple Backups at a time.
  7. Next you can add delete databases backup task and send db mail too.
  8. Check SS below–>Parallel Backup
  9. Download delete databases backup script from here.

Hope this helps!