{"id":44497,"date":"2026-05-14T23:35:41","date_gmt":"2026-05-14T21:35:41","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=44497"},"modified":"2026-05-14T23:46:51","modified_gmt":"2026-05-14T21:46:51","slug":"sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/","title":{"rendered":"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3)"},"content":{"rendered":"\n<p>In the previous section, we discussed the drawbacks of running the commands manually. Indeed, the manual process was taking too much time and could directly impact the database state while the freeze was occurring.<\/p>\n\n\n\n<p>To address this issue, it is possible to automate the solution with PowerShell. The idea is to automate the different operations involved in the snapshot backup and restore process.<\/p>\n\n\n\n<p>We will use two scripts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>One script to perform the backups and create the snapshots.<\/li>\n\n\n\n<li>One script to perform the restores.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-backup-process\">Backup process<\/h2>\n\n\n\n<p>Here is how the backup process works:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We connect to the corresponding SQL Server instance.<\/li>\n\n\n\n<li>We change the state of the database using ALTER DATABASE &#8230; SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. At this point, the I\/Os are frozen.<\/li>\n\n\n\n<li>We connect to the hypervisor through SSH.<\/li>\n\n\n\n<li>We create the snapshot.<\/li>\n\n\n\n<li>We back up the database using BACKUP DATABASE &#8230; WITH METADATA_ONLY.<\/li>\n\n\n\n<li>We change the state of the database using ALTER DATABASE &#8230; SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF. At this point, the I\/Os are unfrozen.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"627\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-1024x627.png\" alt=\"\" class=\"wp-image-44499\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-1024x627.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-300x184.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-768x470.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-1536x941.png 1536w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-2048x1254.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Powershell implementation (backup)<\/h2>\n\n\n\n<p>Here is the code used to perform the backup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>param(\n    &#091;string]$SqlInstance = \"VM-WS25-SQL2\",\n    &#091;string]$Database    = \"StackOverflow\",\n    &#091;string]$BackupDir   = \"D:\\Backups\",\n    &#091;string]$PveHost     = \"192.168.1.110\",\n    &#091;string]$PveUser     = \"MyUser\",\n    &#091;string&#091;]]$Zvols     = @(\"sqlpool\/pve\/vm-302-disk-0\")\n)\n\n$Timestamp = Get-Date -Format \"yyyyMMddTHHmmss\"\n$SnapName  = \"sql_${Database}_${Timestamp}\"\n\n$DbSafe = $Database.Replace(\"]\", \"]]\")\n$BackupFile = Join-Path $BackupDir \"${Database}_${Timestamp}.bkm\"\n\n$ZfsSnapshots = $Zvols | ForEach-Object { \"$_@$SnapName\" }\n$ZfsSnapshotArgs = $ZfsSnapshots -join \" \"\n\n$MediaDescription = \"zfs|$PveHost|$ZfsSnapshotArgs\"\n\n$BackupFileSql = $BackupFile.Replace(\"'\", \"''\")\n$MediaSql = $MediaDescription.Replace(\"'\", \"''\")\n\n$connString = \"Server=$SqlInstance;Database=master;Integrated Security=True;TrustServerCertificate=True;Application Name=ZFS-TSQL-Snapshot;\"\n$conn = New-Object System.Data.SqlClient.SqlConnection $connString\n\nfunction Invoke-SqlNonQuery {\n    param(&#091;string]$Sql)\n\n    $cmd = $conn.CreateCommand()\n    $cmd.CommandTimeout = 0\n    $cmd.CommandText = $Sql\n    &#091;void]$cmd.ExecuteNonQuery()\n}\n\ntry {\n    $conn.Open()\n\n    Write-Host \"Freezing SQL database writes...\"\n    Invoke-SqlNonQuery \"ALTER DATABASE &#091;$DbSafe] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;\"\n\n    Write-Host \"Taking ZFS snapshot on Proxmox...\"\n    ssh \"$PveUser@$PveHost\" \"zfs snapshot $ZfsSnapshotArgs &amp;&amp; zfs hold sqlsnap $ZfsSnapshotArgs\"\n\n    if ($LASTEXITCODE -ne 0) {\n        throw \"ZFS snapshot failed on $PveHost\"\n    }\n\n    Write-Host \"Writing SQL metadata backup...\"\n\n    Invoke-SqlNonQuery @\"\nBACKUP DATABASE &#091;$DbSafe]\nTO DISK = N'$BackupFileSql'\nWITH METADATA_ONLY,\n     MEDIADESCRIPTION = N'$MediaSql',\n     NAME = N'$SnapName';\n\"@\n\n    Write-Host \"Snapshot backup completed:\"\n    Write-Host \"  Snapshot: $ZfsSnapshotArgs\"\n    Write-Host \"  Metadata: $BackupFile\"\n}\ncatch {\n    Write-Warning $_\n\n    try {\n        Write-Warning \"Attempting to unfreeze SQL database...\"\n        Invoke-SqlNonQuery \"ALTER DATABASE &#091;$DbSafe] SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF;\"\n    }\n    catch {\n        Write-Warning \"Could not unfreeze cleanly. Check SQL Server error log.\"\n    }\n\n    throw\n}\nfinally {\n    $conn.Close()\n}<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Restore process<\/h2>\n\n\n\n<p>Here is how the restore process works:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>We connect to the corresponding SQL Server instance.<\/li>\n\n\n\n<li>We take the database offline.<\/li>\n\n\n\n<li>The volume dedicated to the StackOverflow database is taken offline.<\/li>\n\n\n\n<li>We connect to the hypervisor through SSH.<\/li>\n\n\n\n<li>We roll back the corresponding snapshot.<\/li>\n\n\n\n<li>We restore the database using the corresponding backup, which was created at the same time as the snapshot.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"627\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-51-1024x627.png\" alt=\"\" class=\"wp-image-44501\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-51-1024x627.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-51-300x184.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-51-768x470.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-51-1536x941.png 1536w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-51-2048x1254.png 2048w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Powershell implementation (restore)<\/h2>\n\n\n\n<p>Here is the code used to perform the restore:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>param(\n    &#091;string]$SqlInstance = \"VM-WS25-SQL2\",\n    &#091;string]$Database    = \"StackOverflow\",\n    &#091;string]$BackupFile  = \"D:\\Backups\\StackOverflow_20260514T122642.bkm\",\n    &#091;string]$SnapName    = \"sql_StackOverflow_20260514T122642\",\n    &#091;string]$PveHost     = \"192.168.1.110\",\n    &#091;string]$PveUser     = \"MyUser\",\n    &#091;string&#091;]]$Zvols     = @(\"sqlpool\/pve\/vm-302-disk-0\"),\n    &#091;string&#091;]]$DatabaseDriveLetters = @(\"T\"),\n    &#091;switch]$NoRecovery\n)\n\n$ErrorActionPreference = \"Stop\"\n\nfunction Assert-SafeName {\n    param(\n        &#091;string]$Value,\n        &#091;string]$Name,\n        &#091;string]$Pattern\n    )\n\n    if ($Value -notmatch $Pattern) {\n        throw \"$Name contained not allowed characters : $Value\"\n    }\n}\n\nfunction Normalize-DriveLetter {\n    param(&#091;string]$DriveLetter)\n\n    $letter = $DriveLetter.Trim().TrimEnd(\":\").ToUpperInvariant()\n\n    if ($letter -notmatch '^&#091;A-Z]$') {\n        throw \"Drive letter invalid : $DriveLetter\"\n    }\n\n    return $letter\n}\n\nfunction Get-DiskForDriveLetter {\n    param(&#091;string]$DriveLetter)\n\n    $letter = Normalize-DriveLetter $DriveLetter\n\n    $partition = Get-Partition -DriveLetter $letter -ErrorAction Stop\n    $disk = $partition | Get-Disk -ErrorAction Stop\n\n    return &#091;pscustomobject]@{\n        DriveLetter = $letter\n        DiskNumber  = &#091;int]$disk.Number\n        IsOffline   = &#091;bool]$disk.IsOffline\n        FriendlyName = $disk.FriendlyName\n        Size        = $disk.Size\n    }\n}\n\nfunction Invoke-SshChecked {\n    param(&#091;string]$Command)\n\n    Write-Host \"SSH $PveUser@$PveHost :: $Command\"\n\n    &amp; ssh \"$PveUser@$PveHost\" \"$Command\"\n\n    if ($LASTEXITCODE -ne 0) {\n        throw \"SSH command failed with code $LASTEXITCODE : $Command\"\n    }\n}\n\nfunction New-SqlConnection {\n    $connString = \"Server=$SqlInstance;Database=master;Integrated Security=True;TrustServerCertificate=True;Application Name=ZFS-TSQL-Restore-NoVmRestart;\"\n    return New-Object System.Data.SqlClient.SqlConnection $connString\n}\n\nfunction Invoke-SqlNonQuery {\n    param(&#091;string]$Sql)\n\n    $conn = New-SqlConnection\n\n    try {\n        $conn.Open()\n        $cmd = $conn.CreateCommand()\n        $cmd.CommandTimeout = 0\n        $cmd.CommandText = $Sql\n        &#091;void]$cmd.ExecuteNonQuery()\n    }\n    finally {\n        $conn.Close()\n    }\n}\n\nfunction Invoke-SqlScalar {\n    param(&#091;string]$Sql)\n\n    $conn = New-SqlConnection\n\n    try {\n        $conn.Open()\n        $cmd = $conn.CreateCommand()\n        $cmd.CommandTimeout = 0\n        $cmd.CommandText = $Sql\n        return $cmd.ExecuteScalar()\n    }\n    finally {\n        $conn.Close()\n    }\n}\n\nfunction Set-DatabaseDisksOffline {\n    param(&#091;object&#091;]]$DiskInfos)\n\n    $offlinedByScript = @()\n\n    foreach ($diskInfo in ($DiskInfos | Sort-Object DiskNumber -Unique)) {\n        if ($diskInfo.IsOffline) {\n            Write-Host \"Disque $($diskInfo.DiskNumber) d\u00e9j\u00e0 offline. Lecteur $($diskInfo.DriveLetter):\"\n            continue\n        }\n\n        Write-Host \"Taking the Windows disk offline $($diskInfo.DiskNumber), drive $($diskInfo.DriveLetter):\"\n        Set-Disk -Number $diskInfo.DiskNumber -IsOffline $true\n\n        $offlinedByScript += $diskInfo\n    }\n\n    return $offlinedByScript\n}\n\nfunction Set-DatabaseDisksOnline {\n    param(&#091;object&#091;]]$DiskInfos)\n\n    foreach ($diskInfo in ($DiskInfos | Sort-Object DiskNumber -Unique)) {\n        Write-Host \"Bringing the Windows disk back online. $($diskInfo.DiskNumber), drive $($diskInfo.DriveLetter):\"\n        Set-Disk -Number $diskInfo.DiskNumber -IsOffline $false\n    }\n\n    Write-Host \"Update-HostStorageCache...\"\n    Update-HostStorageCache\n}\n\nAssert-SafeName -Value $SnapName -Name \"SnapName\" -Pattern '^&#091;A-Za-z0-9_.:-]{1,160}$'\n\nforeach ($zvol in $Zvols) {\n    Assert-SafeName -Value $zvol -Name \"Zvol\" -Pattern '^&#091;A-Za-z0-9_.:\/-]{1,240}$'\n}\n\n$DbQuoted = \"&#091;\" + $Database.Replace(\"]\", \"]]\") + \"]\"\n$DbLiteral = $Database.Replace(\"'\", \"''\")\n$BackupFileSql = $BackupFile.Replace(\"'\", \"''\")\n\n$ZfsSnapshots = $Zvols | ForEach-Object { \"$_@$SnapName\" }\n$ZfsSnapshotArgs = ($ZfsSnapshots | ForEach-Object { \"'$_'\" }) -join \" \"\n\n$RecoveryOption = if ($NoRecovery) { \"NORECOVERY\" } else { \"RECOVERY\" }\n\n$DatabaseDiskInfos = @()\n$DisksOfflinedByScript = @()\n\nWrite-Host \"\"\nWrite-Host \"Restore SQL Server from a ZFS snapshot, without restarting the VM\"\nWrite-Host \"SQL Instance : $SqlInstance\"\nWrite-Host \"Database     : $Database\"\nWrite-Host \"BackupFile   : $BackupFile\"\nWrite-Host \"DB volumes   : $($DatabaseDriveLetters -join ', ')\"\nWrite-Host \"Snapshots    :\"\n$ZfsSnapshots | ForEach-Object { Write-Host \"  $_\" }\nWrite-Host \"\"\n\ntry {\n    Write-Host \"Checking ZFS snapshots...\"\n    Invoke-SshChecked \"zfs list -H -t snapshot -o name $ZfsSnapshotArgs &gt;\/dev\/null\"\n\n    Write-Host \"Identifying Windows disks containing SQL Server files...\"\n    foreach ($driveLetter in $DatabaseDriveLetters) {\n        $diskInfo = Get-DiskForDriveLetter $driveLetter\n        $DatabaseDiskInfos += $diskInfo\n\n        Write-Host \"Drive $($diskInfo.DriveLetter): -&gt; Windows disk $($diskInfo.DiskNumber) &#091;$($diskInfo.FriendlyName)]\"\n    }\n\n    $backupDrive = $null\n    if ($BackupFile -match '^(&#091;A-Za-z]):\\\\') {\n        $backupDrive = Normalize-DriveLetter $Matches&#091;1]\n\n        try {\n            $backupDiskInfo = Get-DiskForDriveLetter $backupDrive\n            $targetDiskNumbers = @($DatabaseDiskInfos | ForEach-Object { $_.DiskNumber } | Select-Object -Unique)\n\n            if ($targetDiskNumbers -contains $backupDiskInfo.DiskNumber) {\n                throw @\"\nThe backup file $BackupFile is located on drive $backupDrive, which is on the same Windows disk as the SQL Server data volume.\nTaking the data disk offline would make the .bkm file inaccessible, and a rollback could also make the .bkm file disappear.\nMove the .bkm file to C:, a network share, or another disk that is not rolled back.\n\"@\n            }\n        }\n        catch {\n            throw\n        }\n    }\n\n    Write-Host \"Checking whether the SQL Server database exists...\"\n    $DbExists = Invoke-SqlScalar \"SELECT CASE WHEN DB_ID(N'$DbLiteral') IS NULL THEN 0 ELSE 1 END;\"\n\n    if ($DbExists -eq 1) {\n        Write-Host \"Taking database $Database OFFLINE...\"\n        Invoke-SqlNonQuery @\"\nALTER DATABASE $DbQuoted SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\nALTER DATABASE $DbQuoted SET OFFLINE WITH ROLLBACK IMMEDIATE;\n\"@\n    }\n    else {\n        Write-Host \"Database $Database does not exist in SQL Server. Continuing with disk offline and ZFS rollback.\"\n    }\n\n    Write-Host \"Taking Windows disks containing MDF\/LDF files offline...\"\n    $DisksOfflinedByScript = Set-DatabaseDisksOffline -DiskInfos $DatabaseDiskInfos\n\n    Write-Host \"Rolling back ZFS snapshot...\"\n    $RollbackCommands = ($ZfsSnapshots | ForEach-Object { \"zfs rollback -r '$_'\" }) -join \"; \"\n    Invoke-SshChecked \"set -e; $RollbackCommands\"\n\n    Write-Host \"Bringing Windows disks back online...\"\n    Set-DatabaseDisksOnline -DiskInfos $DisksOfflinedByScript\n    $DisksOfflinedByScript = @()\n\n    Write-Host \"Short pause to let Windows and SQL Server detect the restored disk state...\"\n    Start-Sleep -Seconds 5\n\n    Write-Host \"Restoring SQL Server metadata-only backup...\"\n\n    $RestoreSql = @\"\nRESTORE DATABASE $DbQuoted\nFROM DISK = N'$BackupFileSql'\nWITH METADATA_ONLY,\n     REPLACE,\n     $RecoveryOption;\n\"@\n\n    Invoke-SqlNonQuery $RestoreSql\n\n    if (-not $NoRecovery) {\n        Write-Host \"Setting database back to MULTI_USER...\"\n        Invoke-SqlNonQuery @\"\nALTER DATABASE $DbQuoted SET MULTI_USER;\n\"@\n    }\n\n    Write-Host \"\"\n    Write-Host \"Restore completed.\"\n    Write-Host \"Database : $Database\"\n    Write-Host \"Snapshot : $SnapName\"\n    Write-Host \"Backup   : $BackupFile\"\n}\ncatch {\n    Write-Warning \"Restore failed: $_\"\n\n    if ($DisksOfflinedByScript.Count -gt 0) {\n        try {\n            Write-Warning \"Attempting to bring disks offlined by the script back online...\"\n            Set-DatabaseDisksOnline -DiskInfos $DisksOfflinedByScript\n            $DisksOfflinedByScript = @()\n        }\n        catch {\n            Write-Warning \"Unable to automatically bring the disks back online. Check with Get-Disk.\"\n        }\n    }\n\n    try {\n        $DbExistsAfterError = Invoke-SqlScalar \"SELECT CASE WHEN DB_ID(N'$DbLiteral') IS NULL THEN 0 ELSE 1 END;\"\n\n        if ($DbExistsAfterError -eq 1 -and -not $NoRecovery) {\n            Write-Warning \"Attempting to set the database back ONLINE\/MULTI_USER...\"\n            Invoke-SqlNonQuery @\"\nALTER DATABASE $DbQuoted SET ONLINE;\nALTER DATABASE $DbQuoted SET MULTI_USER;\n\"@\n        }\n    }\n    catch {\n        Write-Warning \"Unable to automatically set the database back ONLINE\/MULTI_USER.\"\n    }\n\n    throw\n}<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">What does it look like?<\/h2>\n\n\n\n<p>We start the backup process:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"530\" height=\"82\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-52.png\" alt=\"\" class=\"wp-image-44503\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-52.png 530w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-52-300x46.png 300w\" sizes=\"auto, (max-width: 530px) 100vw, 530px\" \/><\/figure>\n\n\n\n<p>We verify that the snapshot is present:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"750\" height=\"131\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-53.png\" alt=\"\" class=\"wp-image-44504\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-53.png 750w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-53-300x52.png 300w\" sizes=\"auto, (max-width: 750px) 100vw, 750px\" \/><\/figure>\n\n\n\n<p>We verify that the backup is present:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"601\" height=\"36\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-54.png\" alt=\"\" class=\"wp-image-44505\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-54.png 601w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-54-300x18.png 300w\" sizes=\"auto, (max-width: 601px) 100vw, 601px\" \/><\/figure>\n\n\n\n<p>We drop the StackOverflow database:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"314\" height=\"301\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-55.png\" alt=\"\" class=\"wp-image-44506\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-55.png 314w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-55-300x288.png 300w\" sizes=\"auto, (max-width: 314px) 100vw, 314px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"310\" height=\"231\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-56.png\" alt=\"\" class=\"wp-image-44507\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-56.png 310w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-56-300x224.png 300w\" sizes=\"auto, (max-width: 310px) 100vw, 310px\" \/><\/figure>\n\n\n\n<p>We start the restore process:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"951\" height=\"384\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-57.png\" alt=\"\" class=\"wp-image-44508\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-57.png 951w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-57-300x121.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-57-768x310.png 768w\" sizes=\"auto, (max-width: 951px) 100vw, 951px\" \/><\/figure>\n\n\n\n<p>The database is available again. The restore took only a few seconds for a database of approximately 200 GB.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Major drawbacks<\/h2>\n\n\n\n<p>In my case, the solution is executed from the SQL Server itself. Ideally, it should rather be hosted on another server or client machine. We could also imagine running these scripts from a scheduler such as RedDeck, for example.<\/p>\n\n\n\n<p>During the database restore, the database is switched to SINGLE_USER mode. This could be an issue if the applications using the database reconnect very frequently. A better approach would probably be to explicitly terminate the active sessions using the KILL command.<\/p>\n\n\n\n<p>We have also not yet covered the use of a REST API.<\/p>\n\n\n\n<p>Thank you. <a href=\"https:\/\/www.linkedin.com\/in\/amine-haloui-76968056\/\">Amine Haloui<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the previous section, we discussed the drawbacks of running the commands manually. Indeed, the manual process was taking too much time and could directly impact the database state while the freeze was occurring. To address this issue, it is possible to automate the solution with PowerShell. The idea is to automate the different operations [&hellip;]<\/p>\n","protected":false},"author":147,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,42,99],"tags":[272,2974,935],"type_dbi":[2874],"class_list":["post-44497","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-operating-systems","category-sql-server","tag-powershell","tag-proxmox","tag-zfs","type-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.6) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3) - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3)\" \/>\n<meta property=\"og:description\" content=\"In the previous section, we discussed the drawbacks of running the commands manually. Indeed, the manual process was taking too much time and could directly impact the database state while the freeze was occurring. To address this issue, it is possible to automate the solution with PowerShell. The idea is to automate the different operations [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-05-14T21:35:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-05-14T21:46:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-scaled.png\" \/>\n\t<meta property=\"og:image:width\" content=\"2560\" \/>\n\t<meta property=\"og:image:height\" content=\"1568\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Amine Haloui\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Amine Haloui\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/\"},\"author\":{\"name\":\"Amine Haloui\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/221331d69d49c63fca67069b49b813fe\"},\"headline\":\"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\\\/3)\",\"datePublished\":\"2026-05-14T21:35:41+00:00\",\"dateModified\":\"2026-05-14T21:46:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/\"},\"wordCount\":420,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2026\\\/05\\\/image-50-1024x627.png\",\"keywords\":[\"PowerShell\",\"proxmox\",\"ZFS\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"Operating systems\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/\",\"name\":\"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\\\/3) - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2026\\\/05\\\/image-50-1024x627.png\",\"datePublished\":\"2026-05-14T21:35:41+00:00\",\"dateModified\":\"2026-05-14T21:46:51+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/221331d69d49c63fca67069b49b813fe\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2026\\\/05\\\/image-50-scaled.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2026\\\/05\\\/image-50-scaled.png\",\"width\":2560,\"height\":1568},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\\\/3)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/221331d69d49c63fca67069b49b813fe\",\"name\":\"Amine Haloui\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g\",\"caption\":\"Amine Haloui\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/aminehaloui\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3) - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3)","og_description":"In the previous section, we discussed the drawbacks of running the commands manually. Indeed, the manual process was taking too much time and could directly impact the database state while the freeze was occurring. To address this issue, it is possible to automate the solution with PowerShell. The idea is to automate the different operations [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/","og_site_name":"dbi Blog","article_published_time":"2026-05-14T21:35:41+00:00","article_modified_time":"2026-05-14T21:46:51+00:00","og_image":[{"width":2560,"height":1568,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-scaled.png","type":"image\/png"}],"author":"Amine Haloui","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Amine Haloui","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/"},"author":{"name":"Amine Haloui","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe"},"headline":"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3)","datePublished":"2026-05-14T21:35:41+00:00","dateModified":"2026-05-14T21:46:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/"},"wordCount":420,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-1024x627.png","keywords":["PowerShell","proxmox","ZFS"],"articleSection":["Database Administration &amp; Monitoring","Database management","Operating systems","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/","name":"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3) - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-1024x627.png","datePublished":"2026-05-14T21:35:41+00:00","dateModified":"2026-05-14T21:46:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-scaled.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/05\/image-50-scaled.png","width":2560,"height":1568},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server Snapshot Backup and Restore with Proxmox ZFS- Powershell implementation (2\/3)"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/221331d69d49c63fca67069b49b813fe","name":"Amine Haloui","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64707272207cd8d2667aefcb212f3ff5d19a15813da5aad6553f109d1f1afec1?s=96&d=mm&r=g","caption":"Amine Haloui"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/aminehaloui\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/44497","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/147"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=44497"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/44497\/revisions"}],"predecessor-version":[{"id":44564,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/44497\/revisions\/44564"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=44497"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=44497"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=44497"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=44497"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}