Build a Snapshot Backup Catalog in Pure Storage - The SQL Server 2022 Edition

In my previous post, I showed you how to build a snapshot backup catalog using SQL Server 2025’s new native REST API integration. But what if you’re still running SQL Server 2022? Should you miss out on this powerful capability?

Absolutely not.

In this post, I’m going to show you how to achieve the same snapshot backup catalog functionality using PowerShell with SQL Server 2022’s T-SQL Snapshot Backup feature. While we don’t have the native REST integration yet, we can still leverage the power of FlashArray Protection Group Tags to build a queryable snapshot catalog that bridges the gap between database administration and storage management.

The Challenge: Bringing SQL Server 2025 Features to SQL Server 2022

The techniques I demonstrated in the SQL Server 2025 post are pretty cool, but they relied on sp_invoke_external_rest_endpoint - a feature that’s only available in SQL Server 2025. Many organizations are still running SQL Server 2022 and want these capabilities today.

The solution? PowerShell combined with SQL Server 2022’s T-SQL Snapshot Backup feature. By using PowerShell to coordinate the workflow, we can achieve the same rich tagging and catalog functionality while maintaining the application-consistent snapshots that make this approach so powerful.

The PowerShell Approach: Best of Both Worlds

Here’s what we’re accomplishing with this PowerShell-based approach:

  1. Application-consistent database snapshots - Using SQL Server 2022’s T-SQL Snapshot backup
  2. Rich metadata tagging - Tagging snapshots with database context through the FlashArray REST API and Protection Group Snapshot Tags
  3. Queryable snapshot catalog - Building a searchable catalog using Protection Group tags
  4. Automated replication - Ensuring snapshots and their metadata follow your data through your storage environment, on-prem or cloud

The key here is that we can orchestrate the same workflow using PowerShell that SQL Server 2025 does natively. We just need to coordinate the database write freeze, snapshot creation, tagging, and metadata backup ourselves, rather than in native T-SQL.

The Implementation: Step by Step

Now let me walk you through the complete workflow. First, we initialize some variables and connect to our FlashArray.

# Initialize connection variables
$TargetSQLServer = 'aen-sql-22-a'
$ArrayName = 'sn1-x90r2-f06-33.puretec.purestorage.com'
$DbName = 'FT_Demo'
$PGroupName = 'aen-sql-22-a-pg'

# Connect to SQL Server and FlashArray
$Credential = Get-Credential
$SqlInstance = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection
$FlashArray = Connect-Pfa2Array -EndPoint $ArrayName -Credential $Credential -IgnoreCertificateError

Next, we build the database-specific metadata—the keys and values—that will make our snapshot catalog queryable:

# Build comprehensive tagging metadata
$SqlInstanceName = $SqlInstance.Name.Replace('\','_')
$DatabaseName = $DbName
$BackupType = "SNAPSHOT"
$DateStamp = (Get-Date -Format "yyyyMMdd") + "_" + (Get-Date -Format "HHmmss")
$BackupFileName = "$SqlInstanceName" + "_" + "$DatabaseName" + "_" + "$BackupType" + "_" + "$DateStamp" + ".bkm"
$BackupUrl = "s3://s200.fsa.lab/aen-sql-backups/$BackupFileName"

Taking and tagging the Protection Group Snapshot Backup

First, we freeze the database for writes to ensure application consistency, using T-SQL Snapshot Backup:

# Freeze the database for writes
$Query = "ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

While the database is frozen for writes only (typically less than 100ms), we take the snapshot and pass in the tags with the snapshot call. In this example, I’m also forcing replication when the snapshot completes with the -ReplicateNow parameter. In the output below, you can see the new Protection Group snapshot named aen-sql-22-a-pg.4564

# Take snapshot with rich metadata tags
$Snapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArray `
  -SourceName $PGroupName `
  -TagCopyable @($true, $true, $true, $true, $true, $true) `
  -TagKey @('DatabaseName', 'SQLInstanceName', 'BackupType', 'DateStamp', 'BackupFileName', 'BackupUrl') `
  -TagValue @($DbName, $SqlInstanceName, $BackupType, $DateStamp, $BackupFileName, $BackupUrl) `
  -ReplicateNow $true -verbose

$Snapshot

Name              : aen-sql-22-a-pg.4564
Id                : f7c60d28-8606-9a10-885e-2ac822d012c5
Context           : @{Id='ac5fc11f-8b3b-49a0-8261-43baf50b281b'; Name='sn1-x90r2-f06-33'}
Created           : 10/25/2025 1:04:04 PM
Destroyed         : False
EradicationConfig : @{ManualEradication='enabled'}
Pod               : 
Source            : @{Id='53340721-d581-366f-078d-6d6be001fab3'; Name='aen-sql-22-a-pg'}
Space             : 
Suffix            : 4564
TimeRemaining     :

Finally, we take a metadata backup that automatically unfreezes the database for write I/Os and creates an entry in SQL Server’s backup history:

$Query = "BACKUP DATABASE $DbName 
          TO DISK='$BackupUrl' 
          WITH METADATA_ONLY, 
               MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -Verbose

Querying Your Snapshot Catalog

Now with our snapshot completed, we can search for snapshots using database specific information, rather than storage specific information. In the code below, I’m querying our FlashArray REST API to find the most recent Protection Group snapshot for a database named FT_Demo using standard REST API Filtering techniques. And you can see in the returned output, it found the snapshot that we just took named aen-sql-22-a-pg.4564

# Define search criteria
$SqlInstanceName = "aen-sql-22-a"
$DatabaseName = "FT_Demo"

# Build REST API filter
$TagFilter = "tags('default','SQLInstanceName')='$SqlInstanceName' and tags('default','DatabaseName')='$DatabaseName'"

# Query snapshots and get the latest one
$LatestSnapshot = (Get-Pfa2ProtectionGroupSnapshot -Array $FlashArray -Filter $TagFilter -Sort 'created-') | Select-Object -First 1

Name              : aen-sql-22-a-pg.4564
Id                : f7c60d28-8606-9a10-885e-2ac822d012c5
Context           : @{Id='ac5fc11f-8b3b-49a0-8261-43baf50b281b'; Name='sn1-x90r2-f06-33'}
Created           : 10/25/2025 1:04:04 PM
Destroyed         : False
EradicationConfig : @{ManualEradication='enabled'}
Pod               : 
Source            : @{Id='53340721-d581-366f-078d-6d6be001fab3'; Name='aen-sql-22-a-pg'}
Space             : @{Snapshots=0}
Suffix            : 4564
TimeRemaining     :

Output the LatestSnapshot tags

Now that we have the latest snapshot, let’s extract all the metadata information needed to work with this snapshot, things like the database name, its date and time and the location of the backup metadata file which is used to perform the database restore, enabling us to put the database into recovery mode and do things like a point in time recovery or seeding an availability group from snapshot. You can learn more about these techniques in my posts on Performing Point-in-Time Recovery from T-SQL Snapshot Backups and Seeding Availability Group Replicas from T-SQL Snapshot Backups.

$Tags = Get-Pfa2ProtectionGroupSnapshotTag -Array $FlashArray -ResourceName $LatestSnapshot.Name
$Tags | Where-Object { $_.key -in @('DatabaseName','SQLInstanceName','BackupType','DateStamp','BackupFileName','BackupUrl') } |
    Format-Table key, value -AutoSize


Key             Value
---             -----
DatabaseName    FT_Demo
SQLInstanceName aen-sql-22-a
BackupType      SNAPSHOT
DateStamp       20251025_060400
BackupFileName  aen-sql-22-a_FT_Demo_SNAPSHOT_20251025_060400.bkm
BackupUrl       s3://s200.fsa.lab/aen-sql-backups/aen-sql-22-a_FT_Demo_SNAPSHOT_20251025_060400.bkm

In this approach, the tags are replicated with the snapshots. So you can search for your database snapshots on any FlashArray in your fleet. In the code below, I’ve connected to the replication partner of the FlashArray we took the snapshot on, and as you can see in the output below, our snapshot is already on this second FlashArray.

# Search on replication target array
$SecondArrayName = 'sn1-x90r2-f06-27.puretec.purestorage.com'
$SecondFlashArray = Connect-Pfa2Array -EndPoint $SecondArrayName -Credential $Credential -IgnoreCertificateError
$LatestSnapshot = (Get-Pfa2ProtectionGroupSnapshot -Array $SecondFlashArray -Filter $TagFilter -Sort 'created-') | Select-Object -First 1

$LatestSnapshot

Name              : sn1-x90r2-f06-33:aen-sql-22-a-pg.4564
Id                : dcfdf5eb-ee7d-d544-5429-d473fb231253
Context           : @{Id='081f096d-1c16-42a6-9855-92678d705a1c'; Name='sn1-x90r2-f06-27'}
Created           : 10/25/2025 1:04:04 PM
Destroyed         : False
EradicationConfig : @{ManualEradication='enabled'}
Pod               : 
Source            : @{Id='220173ba-d590-d8cf-71d1-0cd37a6b84f8'; Name='sn1-x90r2-f06-33:aen-sql-22-a-pg'}
Space             : @{Snapshots=0}
Suffix            : 4564
TimeRemaining     :

Why This Matters for SQL Server 2022 Environments

This PowerShell-based approach delivers several key advantages:

  1. Immediate availability - No need to wait for SQL Server 2025 upgrades
  2. Application-consistent database snapshots - Leverages SQL Server 2022’s T-SQL Snapshot Backup feature
  3. Rich metadata - Creates the same queryable catalog as the SQL Server 2025 approach
  4. Fleet-wide visibility - Tags replicate with snapshots across your entire storage infrastructure
  5. Familiar tools - Uses PowerShell modules you probably already have

The Performance Story

I’ve been testing this approach with multi-terabyte databases, and the results are impressive. The database freeze time remains in the 100-200ms range, virtually undetectable in most production workloads. What used to be hour-long backup windows become second-long operations.

More importantly, because we’re tagging with comprehensive metadata, finding the right snapshot for recovery becomes trivial. No more complex coordination between storage teams and DBAs trying to figure out which snapshot contains which database.

Getting Started

To implement this in your SQL Server 2022 environment, you’ll need:

  1. SQL Server 2022+ with databases on FlashArray volumes
  2. Pure Storage FlashArray with REST API 2.44+
  3. PowerShell with dbatools and PureStoragePowerShellSDK2 modules
  4. A Protection Group configured for your SQL Server volumes
  5. FlashArray API credentials with appropriate permissions to take snapshots

Looking Forward

While this PowerShell approach gives us immediate access to snapshot catalog capabilities in SQL Server 2022, I’m still excited about the native integration coming in SQL Server 2025. The ability to do everything from within T-SQL without external orchestration will be a game-changer.

But for organizations running SQL Server 2022 today, this PowerShell approach provides a bridge to that future. Delivering the same capabilities while maintaining the application consistency and performance benefits that make snapshot backups so compelling.

The gap between database administration and storage management doesn’t have to exist. With the right approach, we can make our snapshots as database-aware as our traditional backups, regardless of which version of SQL Server we’re running.

T-SQL Snapshot Backup Series

This post is part of my comprehensive series on T-SQL Snapshot Backups. Here are all the posts in the series:

  1. Using T-SQL Snapshot Backup - Are Snapshots Backups? - Introduction to T-SQL Snapshot Backup fundamentals and the theory behind using snapshots as backups
  2. Using T-SQL Snapshot Backup - Point in Time Recovery - Learn how to perform point-in-time recovery using snapshot backups and transaction log backups
  3. Using T-SQL Snapshot Backup - Point in Time Recovery - Azure Edition - Implementing T-SQL Snapshot Backup techniques with Azure Virtual Disks
  4. Using T-SQL Snapshot Backup - Multi-Array Database Snapshots - Coordinating snapshots across multiple Pure Storage FlashArrays for distributed databases
  5. Using T-SQL Snapshot Backup - Seeding Availability Groups - Use snapshot backups to quickly seed new Availability Group replicas
  6. T-SQL REST API Integration in SQL Server 2025: Streamlining T-SQL Snapshot Backups - Leverage SQL Server 2025’s native REST API capabilities for direct storage integration
  7. Building a Snapshot Backup Catalog - Create a queryable snapshot catalog using SQL Server 2025’s native REST API integration
  8. Building a Snapshot Backup Catalog - SQL Server 2022 Edition (This Post) - Achieve the same snapshot catalog functionality using PowerShell with SQL Server 2022

Whether you’re just getting started with snapshot backups or looking to implement advanced catalog functionality, this series will give you the knowledge and tools you need to leverage the power of Pure Storage FlashArrays with SQL Server.