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).
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.
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.
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.
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.
Pablo Echeverria worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implemented new processes, created better monitoring tools and grown my data scientist skills.
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) } }
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
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.
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.
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
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!
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
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
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,
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.
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.
Pablo Echeverria worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implemented new processes, created better monitoring tools and grown my data scientist skills.
