Backup Automation with SQL Server PowerShell and Pure Storage PowerShell

Microsoft SQL Server is one of the primary workloads that Pure Storage customers run on our FlashArray and a frequent question we get is on the topic of automated backup operations. The intent of this sample is to provide DBAs and System Administrators see the potential to combine the automation of specific applications, in this case Microsoft SQL Server, along with leveraging the capabilities of Purity REST API and our Windows PowerShell integration.

The entire process from start to finish for creating a simple database, adding records, taking a FlashRecover Snapshot, creating a new volume from the snapshot, connecting to host and performing backup operations took a total of 1 minute and 34 seconds. That is fast but keep in mind as you go through the sample script the data file is 48GB and the log file is 97GB. It is easy enough to modify those settings for your own use.


In the next several sections I breakdown the individual operations that are performed in the PowerShell script.

The two PowerShell modules used in this sample are the Pure Storage PowerShell Toolkit (version and the SQL Server PowerShell Module (SQLPS).

Import-Module PureStoragePowerShell
Import-Module SQLPS –DisableNameChecking

These next lines are used to set a few different variables so that the script is more easily customizable.

$SQLInstance = “HYPERV-NODE2”
$Array = “”
$MasterDbVolume = “FOOBAR”
$MasterDbVolumeSuffix = “MASTER”
$BackupDbVolume = “FOOBAR-BAK”
$BackupDbName = “FOOBAR-BAK”

I use New-Item to create two new folders to store the database data (MDF) and log (LDF) files.

New-Item -Path “G:\FOOBAR_Data” -ItemType Directory -ErrorAction Ignore
New-Item -Path “G:\FOOBAR_Log” -ItemType Directory -ErrorAction Ignore

You will notice the use of Get-Date in several different sections of the script. The reason behind this is to show the time it takes from beginning to end for each operational section.

$StartDb = Get-Date

The following T-SQL and use of Invoke-SqlCmd create a database named FOOBAR in the specified location.

$TSQL = @”
( NAME = N’foobar_data’, FILENAME = N’G:\FOOBAR_Data\FOOBAR_data.mdf’ , SIZE = 51200000KB , FILEGROWTH = 1048576KB )
( NAME = N’foobar_log’, FILENAME = N’G:\FOOBAR_Log\FOOBAR_log.ldf’ , SIZE = 102400000KB , FILEGROWTH = 10%)
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance

We retrieve another Get-Data then use New-TimeSpan to calculate the time it took from start to end for the database creation. The database created is on a Pure Storage Volume.

$EndDb = Get-Date
$TS1 = New-TimeSpan –Start $StartDb –End $EndDb
Write-Host (“Database created in {0:G}” -f $TS1) -BackgroundColor Yellow -ForegroundColor Black

An empty database is no fun so we use T-SQL and Invoke-SqlCmd to add a few records.

$TSQL = @”
if not exists (select name from sysobjects where name = ‘Sample’ and type = ‘u’)
create table Sample (dt1 datetime)
while (SELECT COUNT(*) from Sample) <= 100
insert Sample select GETDATE()
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance

The screenshot below shows that the database has been created and using the Select Top 1000 Rows from the SQL Server Management Studio menu returns the sample records that were added.


Get a start time as we are beginning a new operation to create a FlashRecover Snapshot, a new volume from that snapshot and connect it to the SQL Server host.

$StartSnaps = Get-Date

$Pwd = ConvertTo-SecureString “pureuser” -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential (“pureuser”, $pwd)
$FlashArray = Get-PfaApiToken -FlashArray $Array -Credential $Creds | Connect-PfaController

New-PfaSnapshot -Volumes $MasterDbVolume -Suffix $MasterDbVolumeSuffix -Session $FlashArray
New-PfaVolume -Name $BackupDbVolume –Source “$MasterDbVolume.$MasterDbVolumeSuffix” -Session $FlashArray
Connect-PfaVolume -Name $SQLInstance -Volume $BackupDbVolume -Session $FlashArray

The below screenshot shows the volumes that were created from the above Pure Storage PowerShell cmdlets.


We retrieve Get-Data then use New-TimeSpan to calculate the time it took from start to end for the snapshot, volume creation and host connection.

$EndSnaps = Get-Date
$TS2 = New-TimeSpan –Start $StartSnaps –End $EndSnaps
Write-Host (“Snapshot, Volume Creation and Host Connected in {0:G}” -f $TS2) -BackgroundColor Yellow -ForegroundColor Black

New operation begins, retrieve another Get-Date.

$StartAttach = Get-Date

Register the newly created volume that has been connected to the SQL Server host instance. The Register-PfaHostVolumes will rescan the SQL Server host and online the new volume.

Register-PfaHostVolumes -Computername $SQLInstance

The following T-SQL attaches the SQL Server database from the previous steps to be used as a database for backups.

$TSQL = @”
USE [master] GO
sp_attach_db @dbname = N’FOOBAR-BAK’,
@filename1 = N’I:\FOOBAR_Data\FOOBAR_data.mdf’,
@filename2 = N’I:\FOOBAR_Log\FOOBAR_log.ldf’
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance

An optional step I placed here was to use the backup database to run a DBCC. Answer Yes or No to process the operation.

$DoDBCC = Read-Host “Would you like to perform a DBCC? Y/N”
If ($DoDBCC -eq “Y”)
$TSQL = @”
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance

Next step it to backup the database and log using the SQLPS cmdlet Backup-SqlDatabase. You will notice that I have set CompressOption = On to use compression on the backup, a specific BlockSize of 16KB (16,384), MaxTransferSize = 64KB (65,536) and to Continue if there are any errors. All of these settings can be modified for any environment. These are some best practices that we recommend.

Backup-SqlDatabase -ServerInstance $SQLInstance `
-BackupFile “$($backupFolder)FOOBAR-BAK_db_data.bak” `
-BackupAction Database `
-Database FOOBAR-BAK `
-CompressionOption On `
-BlockSize 16384 `
-MaxTransferSize 65536 `

Backup-SqlDatabase -ServerInstance $SQLInstance `
-BackupFile “$($backupFolder)FOOBAR-BAK_db_log.bak” `
-BackupAction Log `
-Database FOOBAR-BAK `
-CompressionOption On `
-BlockSize 16384 `
-MaxTransferSize 65536 `

The following screenshot illustrates that the Backup-SqlDatabase cmdlets completed successfully.


An optional step is to detach the database that was used for backups.

$DetachDb = Read-Host “Would you like to detach the backup database? Y/N”
If ($DetachDb -eq “Y”)
$TSQL = @”
USE master;
sp_detach_db @dbname = [FOOBAR-BAK] GO
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance

Retrieve the final Get-Date to do calculation.

$EndAttach = Get-Date
$TS3 = New-TimeSpan –Start $StartAttach –End $EndAttach
Write-Host (“Host Scanned, Database Attached, DBCC and Backup in {0:G}” -f $TS2) -BackgroundColor Yellow -ForegroundColor Black


The full script is available on GitHub in the Toolkit

The below screenshot illustrates the the backup operations completed successfully and can be verified by seeing the restore database task are ready.


I demonstrated this script to my main man @VirtualChappy and he suggested putting a basic PowerShell GUI on this so that is something I’ll work on for a future post. Hope this helps with some of the questions around automation for SQL Server backups.


  • Ian

    I didn’t see anything in this script for freezing/thawing IO on SQL… is this just meant for crash consistent backups?

    • Thanks for your question Ian. Yes, this example is using crash consistent snapshot. It based on a MDF/LDF single volume design. Pure Storage’s FlashArray design provides the ability to consolidate volumes and still provide the performance without LUN sprawl or the use of per-target queues. The last part of the example is based on using native SQL Server backup using SQL PowerShell.


  • Linchi

    I’m playing with SDK and Toolkit, but can’t find cmdlet New-PfaSnapshot anywhere. Is New-PfaSnapshot deprecated?

    • New-PfaSnapshot has been deprecated and the new cmdlet is New-PfaVolumeSnapshot. You can look at the original PowerShell Toolkit which was the foundation for building the newer PowerShell SDK. Then you can do a comparison of the cmdlets.