Using T-SQL Snapshot Backup - Multi-Array Database Snapshots
In this post, the fourth in our series, I want to share an example demonstrating SQL Server 2022’s T-SQL Snapshot Backup feature in a scenario where a database spans multiple storage arrays. If you’re dealing with multi-array environments, you’ll appreciate how this technique freezes database write I/O to take coordinated snapshots across volumes on two FlashArrays. In this post, I’ll walk you through the process, point out some of the script’s key elements, and show you how long the write I/O pause takes.
You can grab the whole script for this blog post here.
The Scenario
Imagine you have a database called MultiArraySnapshot that is distributed across two separate volumes, each located on its own Pure Storage FlashArray. This setup can be beneficial for managing large, mission-critical environments where performance and data consistency are essential. The challenge lies in coordinating a snapshot backup that allows both arrays to capture a consistent view of the data simultaneously. And using SQL Server 2022’s T-SQL Snapshot Backup feature we can do just that…let’s dig in.
Figure 1: A Database with files on Two FlashArrays
Overview of the Process
The process is straightforward: freeze the database using T-SQL Snapshot, take snapshots of each array, and then execute a metadata-only backup to document the snapshot details. This metadata backup automatically thaws the database.
Setting Up the Environment
First, we import the necessary modules and define connection variables for our SQL Server instance, the two FlashArrays, and various volumes. Note how we specify details such as the target disk serial numbers and the backup share path:
Import-Module dbatools
Import-Module PureStoragePowerShellSDK2
# Define target SQL Server and FlashArrays
$TargetSQLServer = 'SqlServer1' # SQL Server Name
$ArrayName1 = 'flasharray1.example.com' # First FlashArray
$ArrayName2 = 'flasharray2.example.com' # Second FlashArray
$PGroupName1 = 'SqlServer1_Pg' # Name of the Protection Group on FlashArray1
$PGroupName2 = 'SqlServer1_Pg' # Name of the Protection Group on FlashArray2
$DbName = 'MultiArraySnapshot' # Name of database
$FlashArray1DbVol = 'Fa1_Sql_Volume_1' # Volume name on FlashArray1 containing database files
$FlashArray2DbVol = 'Fa2_Sql_Volume_1' # Volume name on FlashArray2 containing database files
$BackupShare = '\\FileServer1\SHARE\BACKUP' # File system location to write the backup metadata file
$TargetDisk1 = '6000c296dd4362f1a9263c53f2d9d6c1' # The serial number if the Windows volume containing database files
$TargetDisk2 = '6000c29ef1396de0dad628b856523709' # The serial number if the Windows volume containing database files
We establish a PowerShell remoting session using New-Session and create a persistent SMO connection with Connect-DbaInstance. This connection is essential as it remains open until the backup metadata is written.
$SqlServerSession = New-PSSession -ComputerName $TargetSQLServer
$SqlInstance = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection
Examine the Database File Layout
Next, let’s use Get-DbaDbFile to get detailed information about the database files for the specified database. In our example here P:\SQLDATA1\MultiArraySnapshot.mdf is located on FlashArray1 and Q:\SQLLOG1\MultiArraySnapshot_log.ldf is located on FlashArray2
Get-DbaDbFile -SqlInstance $SqlInstance -Database $DbName |
Select-Object Database, LogicalName, PhysicalName | Format-Table
Database LogicalName PhysicalName
-------- ----------- ------------
MultiArraySnapshot MultiArraySnapshot P:\SQLDATA1\MultiArraySnapshot.mdf
MultiArraySnapshot MultiArraySnapshot_log Q:\SQLLOG1\MultiArraySnapshot_log.ldf
Connecting to the FlashArrays
Now, we authenticate against the REST APIs of our FlashArrays using credentials stored locally. This enables us to perform snapshot operations on the arrays.
$Credential = Import-CliXml -Path "$HOME\FA_Cred.xml"
$FlashArray1 = Connect-Pfa2Array –EndPoint $ArrayName1 -Credential $Credential -IgnoreCertificateError
$FlashArray2 = Connect-Pfa2Array –EndPoint $ArrayName2 -Credential $Credential -IgnoreCertificateError
Freezing the Database
To create a point-in-time snapshot across the two FlashArrays, we first temporarily suspend write I/O by executing the command ALTER DATABASE [MultiArraySnapshot] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This action puts the database in a stable state, preparing it for the snapshots from both FlashArrays. During this process, you can still read from the database.
$Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
VERBOSE: Database 'MultiArraySnapshot' acquired suspend locks in session 58.
VERBOSE: I/O is frozen on database MultiArraySnapshot. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
VERBOSE: Database 'MultiArraySnapshot' successfully suspended for snapshot backup in session 58.
Taking the Snapshots
With the database frozen for write I/O, we now take snapshots on both arrays using New-Pfa2ProtectionGroupSnapshot. These snapshots capture the protection group state.
$SnapshotFlashArray1 = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray1 -SourceName $PGroupName1
$SnapshotFlashArray2 = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray2 -SourceName $PGroupName2
$SnapshotFlashArray1
$SnapshotFlashArray2
Name : SqlServer1_Pg.3044
Created : 2/1/2025 7:39:02 PM
...output omitted
Name : SqlServer1_Pg.39
Created : 2/1/2025 7:39:02 PM
...output omitted
Metadata Backup & Unfreeze
Next, a metadata backup is created. This is the crucial step in generating a backup file (we’ll use this later when restoring this database), automatically unfreezing the database, and recording that snapshot in the database’s backup history.
$Query = "BACKUP DATABASE $DbName
TO DISK='$BackupFile'
WITH METADATA_ONLY"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
VERBOSE: I/O was resumed on database MultiArraySnapshot. No user action is required.
VERBOSE: Database 'MultiArraySnapshot' released suspend locks in session 58.
VERBOSE: Database 'MultiArraySnapshot' originally suspended for snapshot backup in session 58 successfully resumed in session 58.
VERBOSE: Processed 0 pages for database 'MultiArraySnapshot', file 'MultiArraySnapshot' on file 1.
VERBOSE: BACKUP DATABASE successfully processed 0 pages in 0.004 seconds (0.000 MB/sec).
You can check the status of the snapshot backup by reviewing the backup history from the instance using Get-DbaDbBackupHistory.
Get-DbaDbBackupHistory -SqlInstance $SqlInstance -Database $DbName -Last
SqlInstance Database Type TotalSize DeviceType Start Duration End
----------- -------- ---- --------- ---------- ----- -------- ---
aen-sql-22-a MultiArraySnapshot Full 18.65 MB Disk 2025-02-01 19:39:26.000 00:00:00 2025-02-01 19:39:26.000
Let’s Do Something Not So Great…
Now, for demonstration purposes, let’s drop a table.
Invoke-DbaQuery -SqlInstance $SqlInstance -Database $DbName -Query "DROP TABLE T1"
Performing a Restore from a Snapshot Backup on Two Arrays
After dropping the table, let’s restore the whole database from a snapshot. The first thing we need to do is take the database offline.
$Query = "ALTER DATABASE $DbName SET OFFLINE WITH ROLLBACK IMMEDIATE"
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query
Next, we take the volumes that contain our database files offline using Set-Disk, which targets the disks by their serial numbers over a PowerShell remoting connection. While you can run this locally on the SQL Server instance, I am executing this code from my jumpbox.
Invoke-Command -Session $SqlServerSession `
-ScriptBlock {
Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk1 } | Set-Disk -IsOffline $True;
Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk2 } | Set-Disk -IsOffline $True;
}
With the database and its volumes offline, we now use the snapshots on each FlashArray to restore the volumes to their state. Before I delete the table from the database, we will use New-Pfa2Volume for this.
New-Pfa2Volume -Array $FlashArray1 `
-Name $FlashArray1DbVol `
-SourceName ($SnapshotFlashArray1.Name + ".$FlashArray1DbVol") `
-Overwrite $true
Id : 002d6261-ed7f-4623-e723-7c47d71468d6
Name : Fa1_Sql_Volume_1
...output omitted
New-Pfa2Volume -Array $FlashArray2 `
-Name $FlashArray2DbVol `
-SourceName ($SnapshotFlashArray2.Name + ".$FlashArray2DbVol") `
-Overwrite $true
Id : f19a7c65-424d-fe41-3448-e83e94cebb9b
Name : Fa2_Sql_Volume_1
...output omitted
After reverting the volumes on each of the FlashArrays to their previous state via the snapshots, let’s bring the database’s disks online in Windows.
Invoke-Command -Session $SqlServerSession `
-ScriptBlock {
Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk1 } | Set-Disk -IsOffline $False;
Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk2 } | Set-Disk -IsOffline $False;
}
After bringing the disks back online, we restore the database using our metadata backup, using RESTORE DATABASE $DbName FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE
$Query = "RESTORE DATABASE $DbName FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE"
Invoke-DbaQuery -SqlInstance $SqlInstance -Database master -Query $Query -Verbose
VERBOSE: RESTORE DATABASE successfully processed 0 pages in 0.478 seconds (0.000 MB/sec).
Once restored, we check the state of the database MultiArraySnapshot and see that it is ONLINE
Get-DbaDbState -SqlInstance $SqlInstance -Database $DbName
Access : MULTI_USER
ComputerName : aen-sql-22-a
DatabaseName : MultiArraySnapshot
InstanceName : MSSQLSERVER
RW : READ_WRITE
SqlInstance : aen-sql-22-a
Status : ONLINE
And finally, we verify that our dropped table is back in place thanks to the nearly instantaneous restore process.
Get-DbaDbTable -SqlInstance $SqlInstance -Database $DbName -Table 'T1' | Format-Table
ComputerName InstanceName SqlInstance Database Schema Name IndexSpaceUsed DataSpaceUsed RowCount HasClusteredIndex IsFile
Table
------------ ------------ ----------- -------- ------ ---- -------------- ------------- -------- ----------------- ------
aen-sql-22-a MSSQLSERVER aen-sql-22-a MultiArraySnapshot dbo T1 8.00 3864.00 300000 False False
Why Am I Doing This?
This script is important because it shows a way to snapshot and restore a database from snapshot across multiple storage arrays in a SQL Server environment. Here’s why it matters:
- Consistent Snapshots Across Arrays: This example above utilizes SQL Server 2022’s T-SQL Snapshot Backup to pause write I/O on a database with files across two volumes on two different FlashArray. By doing this, snapshots taken on two separate FlashArrays capture the database in a stable state, ensuring that the database is restored reliably.
- Minimized Write I/O Pause: By coordinating a metadata-only backup that automatically unfreezes the database, this process minimizes the window of downtime, which is a critical factor in high availability/high-performance environments.
- Modern SQL Server Capabilities: This section showcases new capabilities in SQL Server 2022 and provides a real-world example of how to use these features to enhance backup strategies and disaster recovery plans in complex deployment scenarios, such as a database spanning two or more arrays.
How Long Does This Take?
This demo runs the code from the freeze until the thaw is around 200ms for the entire snapshot process across both arrays.
$Start = (Get-Date)
$Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
$SnapshotFlashArray1 = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray1 -SourceName $PGroupName1
$SnapshotFlashArray2 = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray2 -SourceName $PGroupName2
$BackupFile = "$BackupShare\$DbName_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName
TO DISK='$BackupFile'
WITH METADATA_ONLY"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose
$Stop = (Get-Date)
Write-Output "The snapshot time takes...$(($Stop - $Start).Milliseconds)ms!"
The snapshot time takes...208ms!
Wrapping Things Up
In this post, the fourth in our series, we used the T-SQL Snapshot Backup feature in a multi-array environment.
You saw how you can:
- Freeze write I/O on a database
- Coordinate snapshots across multiple storage arrays
- Restore quickly using metadata-only backups
You can grab the whole script for this blog post here.
Using T-SQL Snapshot Backup Blog Series
This article is part of a blog series covering the Using T-SQL Snapshot Backup. Check out the other posts in this series:
- Using T-SQL Snapshot Backup - Are Snapshots Backups?
- Using T-SQL Snapshot Backup - Point in Time Recovery
- Using T-SQL Snapshot Backup - Point in Time Recovery - Azure Edition
- Using T-SQL Snapshot Backup - Multi-Array Database Snapshots
You can watch Bob Ward and me present a deep dive on this feature at PASS Summit 2023 in the session “Are Snapshots Backups?”.