Summary
This article takes you through the steps for using a T-SQL script to create application-consistent snapshots on FlashArray from within SQL Server, without needing any external tools or scripts.
This article originally appeared on Anthony Nocentino’s blog. It has been republished with the author’s credit and consent.
In this post, I’ll walk you through a T-SQL script that creates application-consistent snapshots on Pure Storage® FlashArray™, all from within SQL Server, no external tooling. SQL Server 2025 introduces a powerful new feature: the sp_invoke_external_rest_endpoint stored procedure. This enhancement makes it easier than ever to call REST APIs directly from T-SQL. By combining this new capability with the Pure Storage API, we can orchestrate snapshot operations seamlessly—no external tools or scripts required.
If you’ve been following my “Using T-SQL Snapshot Backup” series, you know that this feature offers tremendous benefits for large database environments. Today, we’ll look at how to implement this directly in T-SQL by leveraging SQL Server’s ability to call external REST endpoints, no PowerShell required.
ectly in T-SQL by leveraging SQL Server’s ability to call external REST endpoints, no PowerShell is required.
You can grab the whole script here.
Enabling the REST Endpoint in SQL Server 2025
First, let’s make sure we can connect to external REST endpoints:
|
1 2 3 |
sp_configure ‘external rest endpoint enabled’, 1; RECONFIGURE WITH OVERRIDE; |
This enables the SQL Server REST endpoint feature, which allows our SQL Server to make outbound REST API calls. This is crucial for connecting to the FlashArray’s API to trigger snapshots.
It’s important to note that sp_invoke_external_rest_endpoint requires all endpoints to use HTTPS with TLS encryption, and the certificate must be trusted by the underlying operating system hosting the SQL Server instance.
Authenticating with the FlashArray
Next, we’ll establish a secure session with the Pure Storage FlashArray using SQL Server 2025’s new sp_invoke_external_rest_endpoint stored procedure. This feature represents a significant advancement in SQL Server’s capabilities, allowing us to make REST API calls directly from T-SQL without external scripting.
To authenticate to a FlashArray we first submit an API token with sufficient permissions to the FlashArray’s login endpoint. Upon successful authentication, the FlashArray returns an x-auth-token in the response headers, which becomes our session credential for all subsequent API operations in this session. This token-based authentication model provides a secure, time-limited mechanism for API interactions while maintaining a clear audit trail of operations.
In the code that follows, we’ll execute this authentication request and then extract the x-auth-token from the JSON response to build our authorization headers for subsequent REST API calls.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @ret INT, @response NVARCHAR(MAX), @AuthToken NVARCHAR(100), @MyHeaders NVARCHAR(100); EXEC @ret = sp_invoke_external_rest_endpoint @url = N‘https://flasharray1.purestorage.com/api/2.36/login’, @headers = N‘{“api-token”:”PASTE_YOUR_TOKEN_HERE”}’, @response = @response OUTPUT; PRINT ‘Login Return Code: ‘ + CAST(@ret AS NVARCHAR(10)) PRINT ‘Login Response: ‘ + @response |
Error Handling and Token Extraction
Next, we’ll verify the login’s success and extract the authentication token. If the return code from the FlashArray is anything other than 0, we’ll print an error message and exit. Assuming success, we’ll read the x-auth-token from the login response. Note that the token key must include double quotes "x-auth-token" to ensure JSON_VALUE can parse it correctly. Once extracted, we’ll use the token to construct the authorization header for subsequent REST API calls to the array.
|
1 2 3 4 5 6 7 8 9 10 11 |
if ( @ret <> 0 ) BEGIN PRINT ‘Error in REST call, unable to login to the array.’ RETURN END SET @AuthToken = JSON_VALUE(@response, ‘$.response.headers.”x-auth-token”‘); SET @MyHeaders = N‘{“x-auth-token”:”‘ + @AuthToken + ‘”, “Content-Type”:”application/json”}’ PRINT ‘Headers: ‘ + @MyHeaders |
Freezing the Database for Snapshot
The crucial step comes: suspending the database write I/O to prepare for the snapshot. This command uses SQL Server 2022’s T-SQL Snapshot Backup feature to freeze write I/O operations on the database. The database remains readable, but write operations are suspended until we complete our snapshot process. This ensures we get an application-consistent snapshot.
|
1 2 |
ALTER DATABASE [TestDB1] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON |
Taking the Storage Snapshot
With write I/O frozen on the database, we’re ready to take a snapshot on the FlashArray. The next step is to call the protection-group-snapshots REST endpoint to initiate a snapshot backup of the protection group.
|
1 2 3 4 5 6 7 8 9 |
EXEC @ret = sp_invoke_external_rest_endpoint @url = N‘https://flasharray1.purestorage.com/api/2.36/protection-group-snapshots’, @headers = @MyHeaders, @payload = N‘{“source_names”:”aen-sql-25-a-pg”}’, @response = @response OUTPUT; PRINT ‘Snapshot Return Code: ‘ + CAST(@ret AS NVARCHAR(10)) PRINT ‘Snapshot Response: ‘ + @response |
Here, we’re calling the FlashArray’s REST API protection-group-snapshots endpoint to create a snapshot of a protection group named aen-sql-25-a-pg. The protection group needs to contain all the volumes where our database files are stored. The FlashArray will create a point-in-time snapshot of all volumes in this protection group.
Extracting the Snapshot Name and Creating the Backup
In this next step, we extract the snapshot name from the FlashArray’s JSON response using JSON_VALUE(@response, '$.result.items[0].name'). This name will be included in the Backup Media Description as part of a metadata-only backup. If the FlashArray returns a success code (return code = 0), we proceed with the backup using the BACKUP DATABASE command and the METADATA_ONLY option. This creates a lightweight backup file that contains only the database metadata and a reference to the FlashArray snapshot in the MEDIADESCRIPTION. Storing the snapshot name in the MEDIADESCRIPTION makes identifying and locating the corresponding FlashArray snapshot easier when performing a restore operation.
If the snapshot operation fails, we log an error message and immediately unsuspend the database to resume normal write I/O.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @SnapshotName NVARCHAR(100) SET @SnapshotName = JSON_VALUE(@response, ‘$.result.items[0].name’) if ( @ret = 0 ) BEGIN BACKUP DATABASE [TestDB1] TO DISK=’SnapshotBack.bkm’ WITH METADATA_ONLY, MEDIADESCRIPTION=@SnapshotName PRINT ‘Snapshot backup successful. Snapshot Name: ‘ + @SnapshotName END ELSE BEGIN ALTER DATABASE [TestDB1] SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF PRINT ‘Error in REST call, snapshot backup failed. Database unsuspended.’ END |
Checking the SQL Server Error Log
To verify our backup operation, we check the SQL Server error log.
The SQL Server error log confirms a successful metadata-only snapshot backup of the TestDB1 database. The log shows the database was suspended, write I/O was frozen, and the snapshot was taken before I/O was safely resumed. Since this is a metadata-only backup, no data pages were processed; only metadata was captured and written to the specified .bkm file. This ensures an application-consistent snapshot with minimal impact on database availability.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N‘desc’ 2025–05–09 12:03:25.120 Backup BACKUP DATABASE successfully processed 0 pages in 0.004 seconds (0.000 MB/sec). 2025–05–09 12:03:25.090 Backup Database backed up. Database: TestDB1, creation date(time): 2025/04/30(16:05:02), pages dumped: 394061770, first LSN: 80323:19757:39, last LSN: 80323:19774:1, number of dump devices: 1, device information: (FILE=12, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\SnapshotBack.bkm’}). This is an informational message only. No user action is required. 2025–05–09 12:03:25.080 spid86 Database ‘TestDB1’ originally suspended for snapshot backup in session 86 successfully resumed in session 86. 2025–05–09 12:03:25.080 spid86 Database ‘TestDB1’ released suspend locks in session 86. 2025–05–09 12:03:25.080 spid86 I/O was resumed on database TestDB1. No user action is required. 2025–05–09 12:03:25.080 spid86 I/O is frozen on database TestDB1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. 2025–05–09 12:03:22.110 spid86 Database ‘TestDB1’ successfully suspended for snapshot backup in session 86. 2025–05–09 12:03:22.110 spid86 I/O is frozen on database TestDB1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. 2025–05–09 12:03:22.070 spid86 Database ‘TestDB1’ acquired suspend locks in session 86. 2025–05–09 12:03:22.070 spid86 Setting database option suspend_for_snapshot_backup to ON for database ‘TestDB1’. |
Here is the output of the print statements we used above. The snapshot backup completed successfully: the system authenticated with the FlashArray, suspended TestDB1 to freeze I/O, created the snapshot (aen-sql-25-a-pg.17), resumed I/O, and finished with a metadata-only backup—all confirmed through SQL Server logs and API responses.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Login Return Code: 0 Login Response: {“response”:{“status”:{“http”:{“code”:200,“description”:“”}},“headers”:{“Date”:“Fri, 09 May 2025 12:03:22 GMT”,“Content-Length”:“37”,“Content-Type”:“application\/json”,“Server”:“nginx”,“x-auth-token”:“75b2b984-a238-44ce-adbd-ff19decab148”,“strict-transport-security”:“max-age=31536000; includeSubDomains;”,“content-security-policy”:“frame-ancestors ‘none'”,“x-frame-options”:“DENY”,“x-content-type-options”:“nosniff”,“x-xss-protection”:“1; mode=block”,“x-request-id”:“bdc6bbff70105c00fdeac8b0a1af565c”}},“result”:{“items”:[{“username”:”anocentino”}]}} Headers: {“x-auth-token”:“75b2b984-a238-44ce-adbd-ff19decab148”, “Content-Type”:“application/json”} Database ‘TestDB1’ acquired suspend locks in session 86. I/O is frozen on database TestDB1. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. Database ‘TestDB1’ successfully suspended for snapshot backup in session 86. Snapshot Return Code: 0 Snapshot Response: {“response”:{“status”:{“http”:{“code”:200,“description”:“”}},“headers”:{“Date”:“Fri, 09 May 2025 12:03:22 GMT”,“Content-Length”:“340”,“Content-Type”:“application\/json”,“Server”:“nginx”,“x-auth-token”:“75b2b984-a238-44ce-adbd-ff19decab148”,“strict-transport-security”:“max-age=31536000; includeSubDomains;”,“content-security-policy”:“frame-ancestors ‘none'”,“x-frame-options”:“DENY”,“x-content-type-options”:“nosniff”,“x-xss-protection”:“1; mode=block”,“x-request-id”:“e3089939224baca84d6ad69b485855de”}},“result”:{“items”:[{“name”:”aen-sql-25-a-pg.17″,”id”:”32e86de3-a3ee-3ae0-f74c-4bf5a7f74744″,”space”:null,”source”:{“name”:”aen-sql-25-a-pg”,”id”:”b6660b61-a8de-ae1b-cb65-e3045d13b5ba”},”suffix”:”17″,”destroyed”:false,”created”:1746792202183,”pod”:{“name”:null,”id”:null},”time_remaining”:null,”eradication_config”:{“manual_eradication”:”enabled”}}]}} I/O was resumed on database TestDB1. No user action is required. Database ‘TestDB1’ released suspend locks in session 86. Database ‘TestDB1’ originally suspended for snapshot backup in session 86 successfully resumed in session 86. Processed 0 pages for database ‘TestDB1’, file ‘test_data_01’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_02’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_03’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_04’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_05’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_06’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_07’ on file 12. Processed 0 pages for database ‘TestDB1’, file ‘test_data_08’ on file 12. BACKUP DATABASE successfully processed 0 pages in 0.004 seconds (0.000 MB/sec). Snapshot backup successful. Snapshot Name: aen–sql–25–a–pg.17 |
Wrapping Things Up
This example highlights how SQL Server 2025’s new sp_invoke_external_rest_endpoint procedure transforms backup automation. Previously, integrating with storage systems required PowerShell or external tools. Now, we can make REST API calls natively from T-SQL—streamlining workflows and reducing complexity.
Key benefits:
- Native REST integration: Finally, we can make API calls directly from T-SQL without jumping out to PowerShell or other scripting languages
- Simplified automation: Build your entire backup workflow in one place – no more context switching between different tools
- Reduced dependencies: Eliminate those middle layers between SQL Server and your storage systems – fewer moving parts means fewer potential failure points
- Improved security: Keep your authentication centralized within SQL Server rather than in external script files
- Better performance: Executing operations within SQL Server means lower overhead and faster execution times
This feature eliminates context switching and brings modern automation capabilities into the database engine. Just be sure to securely store API tokens and implement proper error handling in production.
The bottom line is that SQL Server 2025’s REST integration is a huge win for DBAs managing complex, large-scale environments.
Cyber Resilience, Pure and Simple
Fortify your data and guarantee uninterrupted business operations.
Dynamic Storage
Learn more about high-performance, all-flash storage.
