Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Backup SQL Server Databases in Parallel with PowerShell


By:   |   Last Updated: 2017-09-27   |   Comments (13)   |   Related Tips: More > 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.


Last Updated: 2017-09-27


get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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

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

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

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

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

 

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

 

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[email protected](
$(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 05, 2017 - 5:43:32 PM - Pablo Echeverria Back To Top

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 05, 2017 - 4:35:56 PM - josh Back To Top

What pattern would you use to add additional paremeters? 

 


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

 This is freekin awesome.

 


Learn more about SQL Server tools