Backup SQL Server Databases in Parallel with PowerShell

By:   |   Updated: 2017-09-27   |   Comments (17)   |   Related: > PowerShell


Problem

I have a need to decrease the time my SQL Server database backups are taking to run.  I thought about trying to run multiple backups at the same time to see if that would work.  I could have created multiple SQL Server Agent Jobs to run at the same time, but I wanted a more dynamic way to handle this, so I created a PowerShell script that allows processes to run in parallel.

Solution

With PowerShell you can spawn multiple threads to run tasks simultaneously. By implementing this approach using PowerShell, I was able to cut down a process that took over an 1.5 hours to a little over an hour.

Please note that this script was only possible thanks to an article titled “True Multithreading in PowerShell” written by Ryan Witschger (link here).

PowerShell Script to Run Simultaneous Backups

Attached is the full PowerShell script and although several lines are self-explanatory or documented inline, there are others that I want to elaborate.

Variables

Here are the variables in the script:

Variables for PowerShell Script to Run Simultaneous Backups

Most of these have comments or should make sense, but I wanted to point out these:

  • $server - enter the name of the SQL Server instance (for a named instance it should be SERVER\INSTANCE)
  • $database - I am using the master database, because this is where my query executes from
  • $query - I am using sp_databases.  This is a system stored procedure that returns all accessible databases in the instance.
  • $objectExclude - these are the databases I want to skip
  • $objectNameField - this is the column name that has the database name returned from sp_databases.
  • $objectSortExpression - this uses the second column in the result set (0,1,2,etc..) to sort the results
  • $tasks - I will explain this more below.

$tasks

This is a list of actions to be performed on every database that is returned from sp_databases. The database name is put inside variable Args0 which is used in the task scriptToRun parameter.  The tasks I am performing are as follows:

  • backup - this will create a full backup on the local server
  • store - this moves the full backup to an archive location
  • delete - this deletes the local copy of the backup

These are the steps that I do in my environment, but you can configure this based on your needs.

Inside each task we have the following:

  • taskOrder - The first field is the “taskOrder”, note that these don’t need to be sequential (you can comment lines when testing) and you can specify the same “taskOrder” for more than one task (that is going to cause both tasks to run in parallel, like copy to a local and copy to a remote repository at the same time).
  • taskName  - The second field is the “taskName”; this is displayed in a progress bar when you run this script in a PowerShell prompt.
  • maxThreads - The third field is the “maxThreads”, this one allows you to backup multiple databases in parallel, or archive multiple files in parallel, or delete multiple files in parallel. This one needs to be carefully tested, as it is going to consume more resources (CPU, memory, network) when multiple tasks are running at the same time.
  • scriptToRun - The last field is the “scriptToRun”; you must take into account that the command you enter is going to be run as a separate program (so you can’t use variables defined anywhere except the one sent from line 52).

Let's take a closer look at the first task I have setup for the backup.

The values I am using are:

  • taskOrder = 1
  • taskName = backup
  • maxThreads = 1 (note: if I want to run several backups at once, I need to change this to a higher value)
  • scriptToRun = sqlcmd -Q "BACKUP DATABASE [$args0] TO DISK=N''C:\Backup\${args0}.bak'' WITH INIT" -r0 -b

For the scriptToRun I am using sqlcmd to run a BACKUP DATABASE command. Here are the dynamic values used.

  • [$args0] = this is the database name that is being passed
  • ${args0} = this is also the database name that is being passed to be used to name the backup file.  You could add more to the name to include a date as well, but I wanted to keep this example simple.

The sqlcmd options are:

  • -Q = query to run
  • -r0 = return errors
  • -b = terminates batch if there is an error

Running the Parallel SQL Server Backup PowerShell Script

For my test I had the following:

  • 32 databases with an average size of about 2.5GB
  • 78GB in total was backed up 

I opened the script using the PowerShell ISE. After adjusting the variables in the script to meet my needs, when this is run this is the output from PowerShell.

Note that while the 'backup' task is running, the 'store' task is running for a database that has been already been backed up.

Running the Parallel SQL Server Backup PowerShell Script
backup

Here is the comparison between the serial job which is using a SQL Server Agent Job to backup one database at a time, then copying the files and then deleting the local copy.  You can see this took 1 hour and 31 minutes.

duration for traditional backups with SQL Server Agent

Here is another SQL Server Agent Job, but this time using the PowerShell script. I used a maxThreads count = 4.  This took 1 hour and 9 minutes to run.

Job Duration for Parallel Backup PowerShell Script
Next Steps
  • Download the PowerShell script.
  • Modify this tasks per your needs and experiment with other tasks you may want to run in parallel.
  • This script also allows you to backup remote databases that may not have SQL Server Agent.
  • This script can be used to perform actions on other elements; it is not restricted to databases only.
  • The $query variable can be made more complex to check if the databases have already been archived; in that case, if the job is re-run, it’s not going to waste resources but instead work only on what is missing.
  • You can include other steps like DBCC or shrink the database, or restore the archived copy to verify it.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Pablo Echeverria Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2017-09-27

Comments For This Article




Tuesday, April 26, 2022 - 4:13:02 PM - Pablo Echeverria Back To Top (90037)
Hi Ram, yes $database is "master" to run sp_databases, but you can comment out those lines if you will provide the list manually. In order to know why it is not working, take a look at the following lines and add your own debugging to them, $ObjectNameField is "DATABASE_NAME" as returned from sp_databases, $objects is an array with named columns, and $object is the actual database name:

#start processing first task for each database
for ($i=0; $i -lt $objects.length; $i++) {
$object = $objects[$i].$objectNameField
$tasks | where {$_[0] -eq $startAtTask} | foreach { CreateThread $object $i ([array]::IndexOf($tasks, $_)) $_[0] ([ref]$Jobs) }
}

Tuesday, April 26, 2022 - 2:08:38 AM - Ram Back To Top (90035)
Hi Pablo,
Thank you for the information.
The script is able to fetch the databases and assign them to variable $objects. But the backup command is never working and none of the databases are being backed up.

Further to your article, $database is always master as the query sp_databases needs to be executed under master DB only.

Monday, April 25, 2022 - 2:02:36 PM - Pablo Echeverría Back To Top (90033)
Hi Ram, the database names are assigned in this statement:
$objects = @((Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -querytimeout $queryTimeout) | where {$objectExclude -notcontains $_.$objectNameField} | sort $objectSortExpression )

You need to modify it and include your own database names there.

Monday, April 25, 2022 - 10:24:32 AM - Ram Back To Top (90032)
Hi ,
How do I pass the list of databases to be backed up in parallel? args[0] unable to pass the database name

Monday, April 22, 2019 - 9:01:26 AM - Pablo Echeverria Back To Top (79631)

Hi Ram, the error code indicates the database doesn't exist, so please check which database doesn't exist, and try excluding it. Then, you may see why it is failing for such database.

Saturday, April 20, 2019 - 2:51:31 AM - Ram Back To Top (79604)

Hi Pablo,

this works great with default instance. But when we use any named instance it is failing with error.  Can yuou please suggest if we need to do any code changes for the named instance please. 

Msg 911, Level 16, State 11, Server XXXXXXXX, Line 1

-Ram

Thursday, April 11, 2019 - 11:48:57 AM - Pablo Echeverria Back To Top (79534)

Hi AJ, I don't know if you can convert your T-SQL script to powershell to be used as an automated task in TFS. All I can tell you is that, in the script attached to this article, if you change line 15 to create a snapshot instead of a backup, it can work as you expect. Hope this helps!

Thursday, April 11, 2019 - 10:22:53 AM - AJ Back To Top (79532)

Hi Pablo,

Thanks for helping me in getting Snapshot script, but how can I convert the SQL script to powershell script so that I can use as an automated task in TFS

Wednesday, April 10, 2019 - 5:35:25 PM - Pablo Echeverria Back To Top (79524)

Hi AJ, here is a link you will find useful about database snapshots, it includes the command you need to run: https://www.mssqltips.com/sqlservertip/3603/sql-server-database-snapshots-for-data-comparison/

Wednesday, April 10, 2019 - 5:23:00 PM - AJ Back To Top (79523)

Thanks Pablo Echeverria,

Looks like the one you provided is for backup but can you please share me for the Sql Database snapshot only..

Wednesday, April 10, 2019 - 4:54:47 PM - Pablo Echeverria Back To Top (79522)

Hi AJ, in the script provided, you only need to change line 15, instead of making a backup, create a snapshot. Current line 15 is as follows:

@(1, 'backup', 1, 'sqlcmd -Q "BACKUP DATABASE [$args0] TO DISK=N''C:\${args0}.bak'' WITH INIT" -r0 -b')

Also, you need to remove lines 16 and 17, as you won't be transmitting the backup neither deleting it. Hope this helps!

Wednesday, April 10, 2019 - 2:52:02 PM - AJ Back To Top (79519)

Hello I am looking for a powershell script to take the SQL DataBase snapshots, If you can help me would be great.

Below was the sample SQL script, which is working but I need it ina powershell format 

DECLARE  @SourceDatabase varchar(128) = '$TargetDatabaseName' -- Name of the database you want to snapshot from.
,@SnapshotAppendvarchar(128)= '$Build.BuildNumber'-- Add here what you want to append to the database name for the snapshot. (Example: Snap_20161001)
,@FilePathvarchar(200)= NULL-- Edit if you want the snapshot to reside somewhere else. (Example: 'C:\Override\Path\')
,@FileSqlvarchar(3000)= '' -- Leave blank.
,@SnapSqlnvarchar(4000)
,@Debugbit= 0

IF DB_ID(@SourceDatabase) IS NULL
RAISERROR('Database doesn''t exist. Please check spelling and instance you are connected to.',1,1)

--==================================
-- 1) Set the file path location of the snapshot data files.
--==================================

IF @FilePath = ''
SET @FilePath = NULL

--==================================
-- 2) Dynamicly build up a list of files for the database to snapshot.
--==================================

SELECT @FileSql = @FileSql +
CASE -- Case statement used to wrap a comma in the right place.
WHEN @FileSql <> '' 
THEN + ','
ELSE ''
END + '
( NAME = ' + mf.name + ', FILENAME = ''' + ISNULL(@FilePath, LEFT(mf.physical_name,LEN(mf.physical_name)- 4 ) ) + '_' + @SnapshotAppend + '.ss'')'
-- Remove file extension .mdf, .ndf, and add .ss
FROM sys.master_files AS mf
INNER JOIN sys.databases AS db ON db.database_id = mf.database_id
WHERE db.state = 0 -- Only include database online.
AND mf.type = 0 -- Only include data files.
AND db.[name] = @SourceDatabase

--==================================
-- 3) Build the create snapshot syntax.
--==================================
SET @SnapSql =
'
CREATE DATABASE ' + @SourceDatabase + '_' + @SnapshotAppend + '
    ON ' 
+ @FileSql +
'
    AS SNAPSHOT OF '+ @SourceDatabase + ';'

--==================================
-- 4) Print or execute the dynamic sql.
--==================================
IF (@Debug = 1)
BEGIN
PRINT @SnapSql
END
ELSE
BEGIN
EXEC sp_executesql @stmt = @SnapSql
END
GO

Wednesday, January 31, 2018 - 11:48:21 AM - Pablo Echeverria Back To Top (75069)

 

Hi REMBO, I'm afraid this script only works for a single instance, so you would be able to restore several databases in parallel within the same instance. But feel free to check my other tips that connect to multiple servers, maybe you can find something useful there to complete your solution. Best regards,

Wednesday, January 31, 2018 - 10:20:01 AM - REMBO Back To Top (75068)

 

Hey. elegant work, but there is a question. How about recovery, I'm trying to use your script but for recovery from a certain directory. tell me how to do it so that to restore several bases at different instances. there is a ready-made script of mine, but I'm using script blanks that access the directory and restore the databases, given that they have not been created yet. the code below is mine.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCatalog = "master";
$SqlLogin = "sa";
$arrayofDATA=@(
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\analit.sql'
SERVER="."
Pass="321"
}),
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\kassa.sql'
SERVER=".\KASSA"
Pass="123"
}))
 
foreach($data in $arrayofDATA)
{
    $SERV=$data.Server
    $PATH=$data.PATH
    $Pass=$data.Pass
    $SqlConnection.ConnectionString = "Server=$SERV; Database=$SqlCatalog; User ID=$SqlLogin; Password=$Pass;"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandTimeout = 0;
    $SqlCmd.CommandText = [IO.File]::ReadAllText($PATH) ;
    $objReader = $SqlCmd.ExecuteReader()
    while ($objReader.HasRows)
{
 
    while ($objReader.read()) {
    echo $objReader.GetValue(0)
}
$objReader.NextResult();    
}
    $objReader.close()
    $SqlConnection.Close()
}

If it's not difficult, you can tell.
Thank'

Thursday, October 5, 2017 - 5:43:32 PM - Pablo Echeverria Back To Top (66946)

For adding additional parameters, you need to add them below line 52 "$PowershellThread.AddArgument($objectName) | out-null", then you can reference it as "$args[1]" and so on.

Thursday, October 5, 2017 - 4:35:56 PM - josh Back To Top (66942)

What pattern would you use to add additional paremeters? 

 

Thursday, September 28, 2017 - 5:29:50 PM - Paresh Motiwala Back To Top (66691)

 This is freekin awesome.

 














get free sql tips
agree to terms