{"id":44525,"date":"2026-05-14T23:39:18","date_gmt":"2026-05-14T21:39:18","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=44525"},"modified":"2026-05-14T23:47:42","modified_gmt":"2026-05-14T21:47:42","slug":"sql-server-snapshot-backup-and-restore-with-proxmox-zfs-rest-api-with-sql-server-2025-3-3","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-rest-api-with-sql-server-2025-3-3\/","title":{"rendered":"SQL Server Snapshot Backup and Restore with Proxmox ZFS – REST API with SQL Server 2025 (3\/3)"},"content":{"rendered":"\n
The proposed architecture consists in adding a small internal REST API on the Proxmox server in order to expose a controlled ZFS snapshot operation. SQL Server 2025 can then call this API through sp_invoke_external_rest_endpoint, instead of running SSH commands directly or relying on an external tool.<\/p>\n\n\n\n
The role of the API is deliberately limited: it receives a snapshot request, checks that the requested zvol is authorized, and then runs the zfs snapshot command on the Proxmox side. An allowlist is used to restrict the ZFS volumes that can be accessed. This prevents a REST call from being able to manipulate any dataset on the server.<\/p>\n\n\n\n
With this approach, we can reproduce a behavior close to what an enterprise storage array provides, but using Proxmox and ZFS. It is important to note that Proxmox does not natively provide the same level of integration as Pure Storage for SQL Server snapshots. Pure Storage provides dedicated mechanisms and integrations. In our case, we need to build a specific orchestration layer. The REST API therefore acts as an adapter between SQL Server, which drives the snapshot backup workflow, and ZFS, which actually performs the storage-level snapshot.<\/p>\n\n\n\n
Architecture<\/h2>\n\n\n\n
Here is a global overview of the architecture:<\/p>\n\n\n\n
\n
SQL Server freezes the database I\/Os<\/li>\n\n\n\n
SQL Server 2025 calls the internal REST API<\/li>\n\n\n\n
The REST API validates the request and checks the zvol allowlist<\/li>\n\n\n\n
The API triggers the ZFS snapshot on Proxmox<\/li>\n\n\n\n
The API returns the snapshot information to SQL Server<\/li>\n\n\n\n
SQL Server creates the metadata-only backup<\/li>\n\n\n\n
The database I\/Os are released<\/li>\n<\/ul>\n\n\n\n<\/figure>\n\n\n\n
REST API implementation<\/h2>\n\n\n\n
Under Proxmox, we install the required packages:<\/p>\n\n\n\n
Then we create the secured ZFS helper. This script is executed as root through sudo, but it rejects any dataset that is not defined in the allowlist.<\/p>\n\n\n\n
cat >\/usr\/local\/sbin\/sql-zfs-helper <<'EOF'\n#!\/usr\/bin\/env bash\nset -euo pipefail\n\nALLOW_FILE=\"\/etc\/sql-zfs-api\/allowed-zvols\"\nLOCK_FILE=\"\/run\/sql-zfs-helper.lock\"\n\ndie() {\n echo \"$*\" >&2\n exit 1\n}\n\nexec 9>\"$LOCK_FILE\"\nflock -n 9 || die \"another snapshot operation is already running\"\n\n[[ -r \"$ALLOW_FILE\" ]] || die \"allowlist not readable: $ALLOW_FILE\"\n\nmapfile -t ALLOWED_DATASETS < <(grep -Ev '^\\s*(#|$)' \"$ALLOW_FILE\")\n\nis_allowed() {\n local ds=\"$1\"\n local allowed\n for allowed in \"${ALLOWED_DATASETS[@]}\"; do\n [[ \"$ds\" == \"$allowed\" ]] && return 0\n done\n return 1\n}\n\nvalid_snapname() {\n [[ \"$1\" =~ ^[A-Za-z0-9_.:-]{1,120}$ ]]\n}\n\nACTION=\"${1:-}\"\nshift || true\n\ncase \"$ACTION\" in\n snapshot)\n SNAPNAME=\"${1:-}\"\n shift || true\n\n valid_snapname \"$SNAPNAME\" || die \"invalid snapshot name: $SNAPNAME\"\n [[ \"$#\" -ge 1 ]] || die \"no zvol specified\"\n [[ \"$#\" -le 8 ]] || die \"too many zvols\"\n\n SNAPSHOTS=()\n\n for DS in \"$@\"; do\n is_allowed \"$DS\" || die \"dataset not allowed: $DS\"\n \/sbin\/zfs list -H -t volume -o name \"$DS\" >\/dev\/null 2>&1 || die \"zvol not found: $DS\"\n\n FULLSNAP=\"${DS}@${SNAPNAME}\"\n\n if \/sbin\/zfs list -H -t snapshot -o name \"$FULLSNAP\" >\/dev\/null 2>&1; then\n die \"snapshot already exists: $FULLSNAP\"\n fi\n\n SNAPSHOTS+=(\"$FULLSNAP\")\n done\n\n \/sbin\/zfs snapshot \"${SNAPSHOTS[@]}\"\n \/sbin\/zfs hold sqlsnap \"${SNAPSHOTS[@]}\"\n\n printf '{\"status\":\"ok\",\"snapshots\":['\n SEP=\"\"\n for S in \"${SNAPSHOTS[@]}\"; do\n printf '%s\"%s\"' \"$SEP\" \"$S\"\n SEP=\",\"\n done\n printf ']}\\n'\n ;;\n\n list)\n \/sbin\/zfs list -H -t snapshot -o name -r sqlpool | grep '@sql_' || true\n ;;\n\n *)\n die \"usage: sql-zfs-helper snapshot SNAPNAME ZVOL [ZVOL...]\"\n ;;\nesac\nEOF\n\nchown root:root \/usr\/local\/sbin\/sql-zfs-helper\nchmod 750 \/usr\/local\/sbin\/sql-zfs-helper\n<\/code><\/pre>\n\n\n\n
We only allow the helper through sudo:<\/p>\n\n\n\n
Next, we enable HTTPS. SQL Server sp_invoke_external_rest_endpoint calls HTTPS endpoints, and the documentation specifies that only HTTPS endpoints with TLS are supported.<\/p>\n\n\n\n
The \/etc\/sql-zfs-api\/tls.crt certificate must be imported into the Windows trusted root certification authorities on the SQL Server side. Otherwise, the HTTPS call may fail.<\/p>\n\n\n\n
We create the systemd service:<\/p>\n\n\n\n
cat >\/etc\/systemd\/system\/sql-zfs-api.service <<'EOF'\n[Unit]\nDescription=SQL Server to ZFS Snapshot API\nAfter=network-online.target\nWants=network-online.target\n\n[Service]\nUser=sqlsnap\nGroup=sqlsnap\nWorkingDirectory=\/opt\/sql-zfs-api\nEnvironmentFile=\/etc\/sql-zfs-api\/sql-zfs-api.env\nExecStart=\/opt\/sql-zfs-api\/venv\/bin\/uvicorn app:app --host 0.0.0.0 --port 8443 --ssl-keyfile \/etc\/sql-zfs-api\/tls.key --ssl-certfile \/etc\/sql-zfs-api\/tls.crt\nRestart=on-failure\nRestartSec=3\n\n[Install]\nWantedBy=multi-user.target\nEOF\n\nsystemctl daemon-reload\nsystemctl enable --now sql-zfs-api\nsystemctl status sql-zfs-api\n<\/code><\/pre>\n\n\n\n
We check the status of our API:<\/p>\n\n\n\n<\/figure>\n\n\n\n
It is possible to call the API in PowerShell using Invoke-RestMethod with PowerShell 7:<\/p>\n\n\n\n
A certificate was generated on Proxmox and it needs to be imported on the SQL Server host. In my case, it was located here:<\/p>\n\n\n\n<\/figure>\n\n\n\n
I then imported it on Windows Server:<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\n
For testing purposes, I created something simple. On the SQL Server side, we can create a database that will be used to store our future stored procedure. This procedure will allow us to interact with the API. In my case, I created a database called dbi_tools:<\/p>\n\n\n\n<\/figure>\n\n\n\n
This database will contain a credential. In our case, the DATABASE SCOPED CREDENTIAL is used to securely store the authentication information required to call the REST API from SQL Server. This allows us, for example, to protect the API key:<\/p>\n\n\n\n
USE [dbi_tools]\nGO\n\nIF NOT EXISTS (\n SELECT 1\n FROM sys.symmetric_keys\n WHERE name = '##MS_DatabaseMasterKey##'\n)\nBEGIN\n CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStrongPassword_%99';\nEND\nGO\n\nCREATE DATABASE SCOPED CREDENTIAL [https:\/\/192.168.1.110:8443\/v1\/sql-zfs\/snapshot]\nWITH\n IDENTITY = 'HTTPEndpointHeaders',\n SECRET = '{\"x-sqlsnap-key\":\"MyAPIKey\"}';\nGO<\/code><\/pre>\n\n\n\n
We then create a stored procedure to encapsulate the code used to call the API:<\/p>\n\n\n\n
USE dbi_tools;\nGO\n\nCREATE OR ALTER PROCEDURE dbo.usp_BackupDatabase_WithZfsSnapshot\n @DatabaseName sysname,\n @BackupDirectory nvarchar(4000) = N'D:\\Backups\\'\nAS\nBEGIN\n SET NOCOUNT ON;\n\n DECLARE @Url nvarchar(4000) =\n N'https:\/\/192.168.1.110:8443\/v1\/sql-zfs\/snapshot';\n\n DECLARE @Vmid int = 302;\n\n DECLARE @ZvolsJson nvarchar(max) =\n N'[\"sqlpool\/pve\/vm-302-disk-0\"]';\n\n DECLARE @Stamp varchar(20) =\n REPLACE(REPLACE(CONVERT(varchar(19), SYSUTCDATETIME(), 126), '-', ''), ':', '') + 'Z';\n\n DECLARE @SafeDbName nvarchar(128) =\n REPLACE(REPLACE(REPLACE(@DatabaseName, N' ', N'_'), N'[', N''), N']', N'');\n\n DECLARE @SnapName nvarchar(128) =\n CONCAT(N'sql_', @SafeDbName, N'_', @Stamp);\n\n DECLARE @BackupFile nvarchar(4000) =\n CONCAT(@BackupDirectory, N'\\', @SafeDbName, N'_', @Stamp, N'.bkm');\n\n DECLARE @Payload nvarchar(max) =\n (\n SELECT\n @DatabaseName AS [database],\n @Vmid AS [vmid],\n @SnapName AS [snapname],\n JSON_QUERY(@ZvolsJson) AS [zvols]\n FOR JSON PATH, WITHOUT_ARRAY_WRAPPER\n );\n\n DECLARE @ReturnCode int;\n DECLARE @Response nvarchar(max);\n DECLARE @SnapshotList nvarchar(max);\n\n SELECT @SnapshotList =\n STRING_AGG(CONCAT([value], N'@', @SnapName), N';')\n FROM OPENJSON(@ZvolsJson);\n\n DECLARE @MediaDescription nvarchar(max) =\n CONCAT(N'zfs|promox1|', @SnapshotList);\n\n DECLARE @Sql nvarchar(max);\n\n BEGIN TRY\n SET @Sql =\n N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) +\n N' SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;';\n\n EXEC sys.sp_executesql @Sql;\n\n EXEC @ReturnCode = sys.sp_invoke_external_rest_endpoint\n @url = @Url,\n @method = N'POST',\n @headers = N'{\"Content-Type\":\"application\/json\",\"Accept\":\"application\/json\"}',\n @payload = @Payload,\n @credential = [https:\/\/192.168.1.110:8443\/v1\/sql-zfs\/snapshot],\n @timeout = 30,\n @response = @Response OUTPUT;\n\n IF @ReturnCode <> 0\n BEGIN\n DECLARE @Err nvarchar(max) =\n CONCAT(N'ZFS snapshot API failed. ReturnCode=', @ReturnCode, N' Response=', @Response);\n THROW 51001, @Err, 1;\n END;\n\n SET @Sql =\n N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + N'\n TO DISK = @BackupFile\n WITH METADATA_ONLY,\n FORMAT,\n MEDIANAME = @MediaName,\n MEDIADESCRIPTION = @MediaDescription,\n NAME = @BackupName;';\n\n EXEC sys.sp_executesql\n @Sql,\n N'@BackupFile nvarchar(4000),\n @MediaName nvarchar(128),\n @MediaDescription nvarchar(max),\n @BackupName nvarchar(128)',\n @BackupFile = @BackupFile,\n @MediaName = @SnapName,\n @MediaDescription = @MediaDescription,\n @BackupName = @SnapName;\n\n SELECT\n @DatabaseName AS database_name,\n @SnapName AS zfs_snapshot_name,\n @SnapshotList AS zfs_snapshots,\n @BackupFile AS metadata_backup_file,\n @MediaDescription AS media_description,\n @Response AS api_response;\n END TRY\n BEGIN CATCH\n IF DATABASEPROPERTYEX(@DatabaseName, 'IsDatabaseSuspendedForSnapshotBackup') = 1\n BEGIN\n SET @Sql =\n N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) +\n N' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF;';\n\n EXEC sys.sp_executesql @Sql;\n END;\n\n THROW;\n END CATCH\nEND;\nGO\n<\/code><\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-65-998x1024.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-67.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-76.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-69.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-70.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-71-1024x118.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-72.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-73-1024x137.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-74.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-75.png\")
<\/figure>\n\n\n\n