sql server backups in parallel and zip the files

In this post, I will explain and provide a script that allow you to backup SQL Server databases in parallel. SQL Server backups are a key part of any database administrator’s job and one of the first items that will get automated in an environment. In my career, I have seen a number of different approaches to this task, usually a combination of T-SQL and SQL Agent Jobs. The question is, how can we optimize our backup process? When using these, you usually have two different approaches. The first is to create a single script to dynamically identify your databases and back them up serially. The second is to create multiple agent jobs, one for each database, that can execute in parallel, but must be created as static jobs.

With the help of Powershell, we can backup all SQL Server databases in-parallel within a single job and zip all the backup files into a single zip file.

Let’s get started, copy below script and past it into notepad then save it as “backup-sqlserverinparallel.ps1”. Note: you may need to install zip

[CmdletBinding()]
Param(
     [Parameter(Mandatory=$true,Position=0)]
     $SQLInstanceName,
     [Parameter(Mandatory=$true,Position=1)]
     $BckFileDir
)
<#
    Backup-DatabaseInParallel.ps1 'your sql sevrer' 'your directory'
    $SQLInstanceName : your sql server name
    $BckFileDir : your backup directory
    This script will backup your databases in parallel and zip all the backup files into a single zip file.
#>

$SQLQuery = @"
SELECT name,database_id
FROM sys.databases
WHERE name  IN('you db name')
"@

$LocalDir = Get-Location
$dbs=Invoke-Sqlcmd -ServerInstance $SQLInstanceName -Database master -Query $SQLQuery 

WORKFLOW Backup-DatabaseInParallel{
    Param(
        [string]$SQLServerName,
        [ARRAY]$dbs,
        [string]$BckFileDir
    )
    FOREACH -Parallel ($dbname IN $dbs){
        $BckFilenname = "$BckFileDir\$($dbname.name)_$(Get-Date -format "yyyyMMddHHmm").bak"
        $CMD = "BACKUP DATABASE ["+$($dbname.name)+"] TO DISK ='$BckFilenname' WITH  COMPRESSION, STATS = 10;"
        $CMD
        Invoke-Sqlcmd -ServerInstance $SQLServerName -Database master -Query ${CMD} -QueryTimeout 6665323 -ConnectionTimeout 6665323
    }
}

Backup-DatabaseInParallel $SQLInstanceName $dbs $BckFileDir

Set-Location $LocalDir

IF (((Get-ChildItem -path $BckFileDir -Filter "*.bak").FullName.Count) -eq ($dbs.Name.Count)){
    Add-Type -Assembly "System.IO.Compression.FileSystem" ;
    [System.IO.Compression.ZipFile]::CreateFromDirectory("$BckFileDir", ("$BckFileDir" + ((Get-Date).ToString('yyyy-MM-dd'))) + ".zip")
}

Now, let’s setup the backup job by following below steps:

• Copy the script directory name
• Go to SQL Server Agent Job
• Create a new job
• Select Powershell as the Job Type
• You may need to setup Agent proxy account depends on your environments
• Copy below script and past to Agent Job Command area.
• Schedule and save the job.

backup-sqlserverinparallel.ps1 'SQL Server Name Or Host Name' 'Backup Directory Name'

If you want to bypass Agent proxy account. Please copy the entire script and past it to Agent Job Command area. Please do not forget to provide the variables values.

Conclusion:
Overall these are very basic scripts and could be enhanced, but the core functionality is what you should focus on. The emphasis is on making the best use of our tools and resources in the most efficient way possible.