Build a Snapshot Backup Catalog in Pure Storage with SQL Server 2025’s Native REST API

I’m really excited to share some new functionality in SQL Server 2025 combined with some innovations in FlashArray’s REST API.

In this post, I’m going to show you how to build a snapshot backup catalog using FlashArray Protection Group Tags and orchestrating the work using SQL Server 2025’s new native REST integration.

With this solution, you will have the ability to query your snapshots by database name, creation time, instance name, or really any other interesting metadata that you want to add. Bridging the gap between snapshotting volumes and databases. And the best part? You can do this all without external tools or databases - just SQL Server and FlashArray.

The Problem We’re Solving

With the techniques in this post, you can now add information about what’s in the snapshot to the actual snapshot object. You can then come back later and ask the array for a snapshot by information stored in the tags such as database name, creation time, instance name, or really any other interesting metadata that you want to add.

This is a game-changer because it bridges the traditional gap between database administration and storage management. Historically, DBAs think in terms of databases, instances, and backup timestamps, while storage admins think in terms of volumes, arrays, and snapshots. This disconnect often leads to complex coordination challenges during recovery scenarios.

With this tagging system, DBAs can now find and recover databases using familiar database concepts rather than needing to understand storage constructs. The snapshot becomes self-documenting, carrying its database context with it throughout its lifecycle. When the snapshot is replicated to another array in your environment (or even to a completely different data center), this database context travels with it, making your snapshot catalog effectively global across your entire storage fleet.

In the example below, I’ll show you how to implement this bridge between database and storage worlds, effectively building a database snapshot catalog that follows your backups wherever they are replicated to.

Here’s how it works.

SQL Server 2025 + FlashArray Better Together

SQL Server 2025 introduces a new native REST API integration through sp_invoke_external_rest_endpoint, which I discussed in detail in the post T-SQL REST API Integration in SQL Server 2025: Streamlining T-SQL backups. Combining that with Pure Storage FlashArray’s instantaneous, zero-performance-impact snapshots, we get:

  1. Near-instant snapshot backups - even for multi-terabyte databases
  2. Application-consistent snapshots - with minimal write freeze time (typically 10-20ms), Using the T-SQL Snapshot Backup feature. We discussed the write I/O freeze in detail here
  3. Comprehensive metadata tagging - for easy management and discovery of databases contained within snapshots
  4. Seamless integration - no external tools required, just SQL Server and a FlashArray

The Real Game-Changer: Tagging as a Snapshot Catalog

Now, let’s take SQL Server 2025’s new REST capabilities, combine them with T-SQL Snapshot Backup, and add in FlashArray Protection Group Tags. With this combination, you can build a snapshot catalog where the tags hold information about what exactly is in the snapshot, allowing you to query the FlashArray API based on those tags.

The most powerful aspect of this solution is the rich tagging system we can implement. Each snapshot can be tagged with detailed metadata like:

  • Database name
  • SQL instance name
  • Backup timestamp
  • Backup type

This tagging system effectively creates a queryable catalog of snapshots available via the FlashArray REST API, allowing you to search and filter snapshots using any combination of these attributes. Since these tags are replicated with the snapshots, this catalog functionality is available on any FlashArray where snapshots are replicated to.

For example, you can easily:

  • Find all snapshots for a specific database or a set of databases
  • Find the most recent snapshot for a specific database or set of databases
  • Locate the most recent snapshot for all of the databases on a particular SQL instance
  • Identify snapshots taken during a specific time period

Using this technique you can build a system that will allow you to easily discover where your database’s snapshots are anywhere in your FlashArray fleet.

Creating Tagged Protection Group Snapshots

Now let’s get started, calling the REST endpoint to execute our snapshot backup. But the real magic happens when we add tags to these snapshots, describing what’s inside the snapshot. Adding tags is like adding a detailed label to a backup that follows the snapshot wherever it goes.

The first script (take-snapshot.sql) is a worked example demonstrates how to create and tag a Protection Group snapshot. In this example, we’re creating a snapshot of a Protection Group that contains all volumes for our SQL Server database, and adding rich tags that describe what’s inside the snapshot. This code creates a snapshot with Tags on flasharray1.fsa.lab and replicates the snapshot and its tags to flasharray2.fsa.lab.

Here’s an overview of the process followed by a snippet of the code from the example in (take-snapshot.sql).

  1. Set up database metadata - Gather information about the database instance, name, and timestamp for tagging
  2. Create a unique backup identifier - Generate a standardized filename and URL for potential restore operations
  3. Build the REST API payload - Construct a JSON payload with snapshot parameters and detailed metadata tags
  4. Enable snapshot replication - Set replicate_now: true to immediately copy the snapshot to another array
  5. Ensure tag portability - Mark all tags as copyable: true so they follow the snapshot during replication
DECLARE @InstanceName   NVARCHAR(128) = REPLACE(@@SERVERNAME, '\', '_');
DECLARE @DatabaseName   NVARCHAR(128) = 'TPCC-4T';
DECLARE @BackupType     NVARCHAR(20)  = 'SNAPSHOT';
DECLARE @DateStamp      NVARCHAR(20)  = REPLACE(CONVERT(NVARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR, GETDATE(), 108), ':', ''), ' ', '_');
DECLARE @BackupFileName NVARCHAR(255) = @InstanceName + '_' + @DatabaseName + '_' + @BackupType + '_' + @DateStamp + '.bkm';
DECLARE @BackupUrl      NVARCHAR(512) = 's3://s200.fsa.lab/aen-sql-backups/' + @BackupFileName;
DECLARE @Payload        NVARCHAR(MAX);

-- Build a comprehensive payload with all important backup values
SET @Payload = N'{  
    "source_names": "aen-sql-25-a-pg",
    "replicate_now": true,
    "tags": [
        {"copyable": true, "key": "DatabaseName", "value": "' + @DatabaseName + '"},
        {"copyable": true, "key": "SQLInstanceName", "value": "' + @InstanceName + '"},
        {"copyable": true, "key": "BackupTimestamp", "value": "' + @DateStamp + '"},
        {"copyable": true, "key": "BackupType", "value": "' + @BackupType + '"},
        {"copyable": true, "key": "BackupUrl", "value": "' + @BackupUrl + '"}
    ]
}';
PRINT 'Payload: ' + @Payload;

EXEC @ret = sp_invoke_external_rest_endpoint
    @url = N'https://flasharray1.fsa.lab/api/2.44/protection-group-snapshots',
    @headers = @MyHeaders,
    @payload = @Payload,
    @response = @response OUTPUT;

PRINT 'Snapshot Return Code: ' + CAST(@ret AS NVARCHAR(10))
PRINT 'Snapshot Response: ' + @response

Retrieving Protection Group Snapshots by Tag Values

The second script (get-snapshot.sql) shows how to query Protection Group Snapshots via the FlashArray REST API. What I’m doing in the code below is asking the replication target FlashArray’s API for the most recent snapshot (&sort=created-) where SQLInstanceName equals aen-sql-25-a and DatabaseName equals TPCC-4T.

Here’s what the code does:

  1. Query snapshots by tag values - Filter snapshots based on database name and instance name to find relevant backups sorted by time created descending
  2. Extract the most recent snapshot details - Use SQL Server’s JSON functions to parse the API response and get the most recent snapshot name
  3. Retrieve detailed tag information - Make a second API call to get all metadata tags for the selected Protection Group snapshot
  4. Parse tags into a readable format - Use SQL Server’s pivot functionality to format the tag information in a more useful way
  5. Extract specific metadata - Get the backup URL from the tags for use in restore operations
DECLARE @ProtectionGroup NVARCHAR(255) = 'aen-sql-25-a-pg';
DECLARE @APIEndpoint     NVARCHAR(MAX) = N'https://flasharray2.fsa.lab/api/2.44/protection-group-snapshots';
DECLARE @TagFilter       NVARCHAR(MAX) = N'?filter=tags(''default'',''SQLInstanceName'')=''aen-sql-25-a'' and tags(''default'',''DatabaseName'')=''TPCC-4T''&sort=created-';
DECLARE @FullUrl         NVARCHAR(MAX) = @APIEndpoint + @TagFilter;

EXEC sp_invoke_external_rest_endpoint
    @url = @FullUrl,
    @headers = @MyHeaders,
    @method = N'GET', 
    @response = @response OUTPUT;

DECLARE @MostRecentSnapshotName NVARCHAR(255);
SET @MostRecentSnapshotName = JSON_VALUE(@response, '$.result.items[0].name');

PRINT 'Most Recent Snapshot Name: ' + @MostRecentSnapshotName;

DECLARE @SnapshotUrl NVARCHAR(MAX) = N'https://flasharray2.fsa.lab/api/2.44/protection-group-snapshots/tags?resource_names=' + @MostRecentSnapshotName;

EXEC sp_invoke_external_rest_endpoint
    @url = @SnapshotUrl,
    @headers = @MyHeaders,
    @method = N'GET',
    @response = @response OUTPUT;
    
DECLARE @items NVARCHAR(MAX) = JSON_QUERY(@response, '$.result.items');

-- Display tags in a pivoted format
WITH Flattened AS (
    SELECT 
        JSON_VALUE(item.value, '$.context.name') AS ContextName,
        JSON_VALUE(item.value, '$.namespace') AS Namespace,
        JSON_VALUE(item.value, '$.resource.name') AS ResourceName,
        JSON_VALUE(item.value, '$.resource.id') AS ResourceId,
        JSON_VALUE(item.value, '$.key') AS TagKey,
        JSON_VALUE(item.value, '$.value') AS TagValue,
        JSON_VALUE(item.value, '$.copyable') AS Copyable
    FROM OPENJSON(@items) AS item
)
SELECT *
FROM (
    SELECT *
    FROM Flattened
) AS SourceTable
PIVOT (
    MAX(TagValue)
    FOR TagKey IN (
        [DatabaseName],
        [SQLInstanceName],
        [BackupTimestamp],
        [BackupType],
        [BackupUrl]
    )
) AS PivotTable;

DECLARE @SnapshotBackupUrl NVARCHAR(512);

SELECT @SnapshotBackupUrl = JSON_VALUE(item.value, '$.value')
FROM OPENJSON(@response, '$.result.items') AS item
WHERE JSON_VALUE(item.value, '$.key') = 'BackupUrl';

PRINT 'Backup URL: ' + @SnapshotBackupUrl;

Running the code above, you’ll get the most recent Protection Group snapshot for the TPCC-4T database, we next need to get the additional tagging information out of the Protection Group snapshot object. You can then initialize a database restore from snapshot using a cloning process.

Why This Matters

This integration between SQL Server 2025 and Pure Storage offers some pretty cool capabilities:

  1. Instantaneous backups - No more backup windows impacting production
  2. Space efficiency - Pure snapshots only store changes
  3. Zero performance impact - Unlike traditional streaming backups
  4. Rich metadata tagging - Creating a powerful snapshot catalog
  5. SQL Server aware snapshot backups - with SQL Server’s backup history

I’ve been testing this with large databases (several TB) and seeing backup times go from hours to seconds. The performance impact during the snapshot is virtually undetectable, shows up as LOGWRITER waits since it’s only writes that are frozen. Further, the replication between the two arrays is storage efficient, sending only the changed blocks since the last snapshot…helping you get that backup off the primary array as fast as possible and replicating it to another array or another site if required.

Getting Started

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

  1. SQL Server 2025 (preview available now)
  2. Pure Storage FlashArray with REST API 2.44+
  3. A Protection Group already configured on your array
  4. API token with appropriate permissions
  5. external rest endpoint enabled server configuration

Conclusion

The combination of SQL Server 2025’s native REST integration and Pure Storage’s snapshot technology with tagging bridges a contextual gap between storage and databases. By transforming snapshots into a queryable catalog, we’ve eliminated the need for external tools or databases to track our backups.