Using T-SQL Snapshot Backup - Hyper-V Edition

FlashArray, Pure Storage, PowerShell, SQL Server, Hyper-V, Using T-SQL Snapshot Backup
Page content

If you’ve been following my T-SQL Snapshot Backup series, you’ve seen this technique work on bare-metal and standard VM deployments where database files live on volumes directly presented to the SQL Server OS. In this post, I’m bringing T-SQL Snapshot Backup into a Hyper-V cluster environment, with database files on VHDXs backed by a Pure Storage FlashArray Cluster Shared Volume (CSV). Hyper-V adds a few extra layers to manage at the hypervisor level, but the SQL Server side of the story is identical. Let’s walk through it.

The Storage Topology

Before we get into the code, let’s understand the environment. In this lab I have a two-node Hyper-V cluster. SQL Server runs inside a VM on each node. The database files for TPCC-4T live on two VHDXs that are stored on a Pure Storage FlashArray Cluster Shared Volume named hyperv-csv-01-DATA-PROD.

Here’s what the storage stack looks like:

FlashArray Volume (hyperv-csv-01-DATA-PROD)
    └── Cluster Shared Volume (CSV) → mounted at C:\ClusterStorage\Volume10 on HV Node 1
            └── VHDX files
                    ├── hv-sql-01-Data.vhdx  -> presented to hv-sql-01 as D: (data)
                    └── hv-sql-01-Log.vhdx   -> presented to hv-sql-01 as L: (log)
                    └── SQL Server: TPCC-4T

For the restore target, there’s a second CSV backed by a separate FlashArray volume named (hyperv-csv-01-DATA-DEV) presented to hv-sql-02. We’re going to clone the production snapshot over that target volume and use hv-sql-02 as the restore target for point-in-time recovery.

One thing worth noting here: in previous posts in this series, I used New-Pfa2ProtectionGroupSnapshot to snapshot a Protection Group that contained separate data and log volumes. In this environment, both the data VHDX and log VHDX live on the same FlashArray volume, the CSV. So a single volume snapshot captures both files in a consistent state. That’s why I’m using New-Pfa2VolumeSnapshot instead of a Protection Group snapshot. However, if your database spans multiple CSVs you WILL need to ensure those CSVs are all in a Protection Group.

Initialize the Script’s Variables and Connections

To begin, let’s set up the PowerShell variables. I’m using dbatools and the Pure Storage PowerShell SDK2 to manage this.

Import-Module dbatools
Import-Module PureStoragePowerShellSDK2

$SourceSQLServer   = 'hv-sql-01'                           # SQL Server hosting the source database
$TargetSQLServer   = 'hv-sql-02'                           # SQL Server that will receive the clone
$ArrayName         = 'flasharray1.fsa.lab'                 # FlashArray endpoint
$DbName            = 'TPCC-4T'                             # Database name
$BackupShare       = '\\jump-01\BACKUP'                    # UNC path for metadata and log backups

$HVNode2           = 'sql-fci-02.fsa.lab'                  # Hyper-V cluster node that owns the target CSV
$TargetVM          = 'hv-sql-02'                           # VM name receiving the cloned VHDXs
$TargetCSVResource = 'Cluster Disk 11'                     # Cluster resource name for the target CSV

$SourceVolName     = 'hyperv-csv-01-DATA-PROD'             # FA volume backing the source CSV
$TargetVolName     = 'hyperv-csv-01-DATA-DEV'              # FA volume backing the target CSV

$ClonedDataVhdx    = 'C:\ClusterStorage\Volume11\hv-sql-01\hv-sql-01-Data.vhdx'
$ClonedLogVhdx     = 'C:\ClusterStorage\Volume11\hv-sql-01\hv-sql-01-Log.vhdx'

$DataCtrlNum = 0; $DataCtrlLoc = 1
$LogCtrlNum  = 0; $LogCtrlLoc  = 2

Build Connections

I need three things: a PowerShell remoting session to the Hyper-V cluster node, persistent SMO connections to both SQL Server instances, and a connection to the FlashArray REST API.

For the SQL Server connections I’m using Connect-DbaInstance with -NonPooledConnection. This is important. SUSPEND_FOR_SNAPSHOT_BACKUP is session-scoped: the database stays frozen for write IO until the same session executes BACKUP DATABASE ... WITH METADATA_ONLY. If you use a tool that disconnects between calls, the freeze is released immediately. Since dbatools maintains the connection, the database will remain frozen after Invoke-DbaQuery returns.

$HVSession = New-PSSession -ComputerName $HVNode2

$SqlInstance1 = Connect-DbaInstance -SqlInstance $SourceSQLServer -TrustServerCertificate -NonPooledConnection
$SqlInstance2 = Connect-DbaInstance -SqlInstance $TargetSQLServer -TrustServerCertificate -NonPooledConnection

$FACred     = Import-CliXml -Path "$HOME\FA_Cred.xml"
$FlashArray = Connect-Pfa2Array -EndPoint $ArrayName -Credential $FACred -IgnoreCertificateError

Let’s get some information about the source database before we do anything. Take note of the size and status.

Get-DbaDatabase -SqlInstance $SqlInstance1 -Database $DbName |
    Select-Object Name, SizeMB, Status

Name     SizeMB      Status
----     ------      ------
TPCC-4T  4043579.50  Normal

Taking a T-SQL Snapshot Backup

Now let’s take the snapshot backup. I’m wrapping the write IO freeze-snapshot-backup sequence in a timing block so we can measure how long SQL Server’s write IO is actually frozen. For this 4 TB database in my lab, the total freeze-to-backup-completion window runs about 4 seconds, dominated by the metadata backup write to the UNC share. The FlashArray snapshot itself is instantaneous.

Note on Hyper-V CSV cache: SUSPEND_FOR_SNAPSHOT_BACKUP freezes SQL Server’s write IO, but it doesn’t flush the Hyper-V CSV I/O cache. If CSV caching is enabled on your cluster, issue a Checkpoint-VM (Production Checkpoint) before the freeze to ensure the cache is flushed to the volume first. If CSV caching is disabled (which is a best practice with high-performance storage like FlashArray), no action is needed.

Freeze the Database for Write IOs

$Start = (Get-Date)

$Query = "ALTER DATABASE [$DbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstance1 -Query $Query -Verbose

VERBOSE: Database 'TPCC-4T' acquired suspend locks in session 95.
VERBOSE: I/O is frozen on database TPCC-4T. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
VERBOSE: Database 'TPCC-4T' successfully suspended for snapshot backup in session 95.

The database is now frozen for write IO. Reads continue without interruption.

Take the FlashArray Snapshot

With the database frozen for write IOs, take the volume snapshot. This captures both VHDXs in a consistent state because they live on the same FlashArray volume.

$Snapshot = New-Pfa2VolumeSnapshot -Array $FlashArray -SourceName $SourceVolName
$Snapshot

Name      : hyperv-csv-01-DATA-PROD.23
Created   : 5/8/2026 7:56:37 PM
Destroyed : False
Source    : @{Name='hyperv-csv-01-DATA-PROD'}

Store this in $Snapshot; we’ll encode the snapshot name in the backup header next.

Write the Metadata Backup

$BackupFile = "$BackupShare\${DbName}_$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE [$DbName]
          TO DISK='$BackupFile'
          WITH METADATA_ONLY,
               MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstance1 -Query $Query -Verbose

VERBOSE: I/O was resumed on database TPCC-4T. No user action is required.
VERBOSE: Database 'TPCC-4T' released suspend locks in session 95.
VERBOSE: Database 'TPCC-4T' originally suspended for snapshot backup in session 95 successfully resumed in session 95.
VERBOSE: BACKUP DATABASE successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).

$Stop = (Get-Date)
Write-Output "The snapshot time takes...$(($Stop - $Start).TotalMilliseconds)ms!"

The snapshot time takes...4021ms!

Notice MEDIADESCRIPTION='$($Snapshot.Name)|$($FlashArray.ArrayName)'. We’re encoding the snapshot name and the array name in the backup header as a pipe-delimited string. Later, when we’re at the restore target and may not have the $Snapshot variable in scope, we can read this string back directly from the metadata file using RESTORE LABELONLY. This is a crucial part of any snapshot backup plan. It lets you accurately identify the snapshot that belongs to this backup header.

What Does SQL Server Think Happened?

Let’s check the error log.

Get-DbaErrorLog -SqlInstance $SqlInstance1 -LogNumber 0 | Format-Table

ComputerName  Source       Text
------------  ------       ----
hv-sql-01     spid95       Setting database option suspend_for_snapshot_backup to ON for database 'TPCC-4T'.
hv-sql-01     spid95       Database 'TPCC-4T' acquired suspend locks in session 95.
hv-sql-01     spid95       I/O is frozen on database TPCC-4T. No user action is required.
hv-sql-01     spid95       Database 'TPCC-4T' successfully suspended for snapshot backup in session 95.
hv-sql-01     spid95       I/O was resumed on database TPCC-4T. No user action is required.
hv-sql-01     spid95       Database 'TPCC-4T' released suspend locks in session 95.
hv-sql-01     Backup       BACKUP DATABASE successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).

The backup is recorded in MSDB as a full backup with snapshot.

Get-DbaDbBackupHistory -SqlInstance $SqlInstance1 -Database $DbName -Last

SqlInstance    Database  Type TotalSize DeviceType Start                   Duration End
-----------    --------  ---- --------- ---------- -----                   -------- ---
hv-sql-01      TPCC-4T   Full 2.94 TB   Disk       2026-05-08 12:56:37.000 00:00:03 2026-05-08 12:56:40.000

Let’s also look at the backup header. The format is consistent with any other SQL Server backup, which makes it straightforward to build automation around.

Read-DbaBackupHeader -SqlInstance $SqlInstance1 -Path $BackupFile |
    Select-Object BackupType, BackupPath, BackupSize, DatabaseName, ServerName

BackupType  BackupPath                                     BackupSize  DatabaseName  ServerName
----------  ----------                                     ----------  ------------  ----------
1           \\jump-01\BACKUP\TPCC-4T_20260508T...bkm       2.94 TB     TPCC-4T       hv-sql-01

Taking a Log Backup

Now that we have a snapshot backup as the base, let’s take a log backup to extend the restore chain.

$LogBackup = Backup-DbaDatabase -SqlInstance $SqlInstance1 `
    -Database $DbName `
    -Type Log `
    -Path $BackupShare `
    -CompressBackup

$LogBackup

ComputerName  InstanceName SqlInstance   Database TotalSize Type Start                   Duration End
------------  ------------ -----------   -------- --------- ---- -----                   -------- ---
hv-sql-01     MSSQLSERVER  hv-sql-01     TPCC-4T  754.00 KB Log  2026-05-08 13:25:55.000 00:00:01 2026-05-08 13:25:56.000

Performing a Point-in-Time Restore on SQL 02

Here’s where the Hyper-V environment diverges from the bare-metal story in earlier posts in this series. In those posts, the restore preparation was essentially: offline the database, call Get-Disk | Set-Disk -IsOffline $true, clone the snapshot, bring the disk back online. In a Hyper-V environment with a CSV, we have more pieces to manage:

  1. Offline the database on the restore target
  2. Detach the VHDXs from the target VM
  3. Offline the CSV cluster resource
  4. Clone the FlashArray snapshot over the target volume
  5. Online the CSV cluster resource
  6. Re-attach the VHDXs to the target VM
  7. Restore with METADATA_ONLY, NORECOVERY
  8. Restore the log backup
  9. Bring the database online

Let’s work through it step by step.

Retrieve the Snapshot Name from the Backup File

We don’t need to have the $Snapshot variable in scope. We encoded everything we need in MEDIADESCRIPTION. Let’s read it back.

$Query = "RESTORE LABELONLY FROM DISK = '$BackupFile'"
$Labels = Invoke-DbaQuery -SqlInstance $SqlInstance2 -Query $Query -Verbose
$SnapshotName = (($Labels | Select-Object MediaDescription -ExpandProperty MediaDescription).Split('|'))[0]
$ArrayName    = (($Labels | Select-Object MediaDescription -ExpandProperty MediaDescription).Split('|'))[1]
$SnapshotName
$ArrayName

hyperv-csv-01-DATA-PROD.23
flasharray1.fsa.lab

Now we know exactly which snapshot belongs to this backup. If you have many snapshots on the array, this is how you find the right one.

Offline the Database on SQL 02

$Query = "ALTER DATABASE [$DbName] SET OFFLINE WITH ROLLBACK IMMEDIATE"
Invoke-DbaQuery -SqlInstance $SqlInstance2 -Query $Query
$RestoreStart = (Get-Date)

Setting the database offline releases SQL Server’s file handles so the VHDXs can be cleanly detached from the VM. $RestoreStart is set here so the timer covers the full storage swap.

Detach the VHDXs from the Target VM

We run this through the PowerShell remoting session to the Hyper-V host node, using Remove-VMHardDiskDrive to pull the VHDXs off the target VM at the hypervisor level.

Invoke-Command -Session $HVSession -ScriptBlock {
    param($vm)
    Remove-VMHardDiskDrive -VMName $vm -ControllerType SCSI -ControllerNumber 0 -ControllerLocation 1
    Remove-VMHardDiskDrive -VMName $vm -ControllerType SCSI -ControllerNumber 0 -ControllerLocation 2
} -ArgumentList $TargetVM

Offline the Target CSV

The FlashArray volume can’t be overwritten while it’s in use as a CSV. Take the cluster resource offline first.

Invoke-Command -Session $HVSession -ScriptBlock {
    param($res)
    Stop-ClusterResource -Name $res -Cluster (Get-Cluster).Name | Out-Null
    Write-Output "$res offline"
} -ArgumentList $TargetCSVResource

Cluster Disk 11 offline

Clone the Snapshot to the Target Volume

This is the instantaneous part. New-Pfa2Volume with -Overwrite $true reverts the target volume to the state of the snapshot. On a FlashArray this is metadata-only. No data is copied. It completes in milliseconds regardless of how large the volume is.

New-Pfa2Volume -Array $FlashArray -Name $TargetVolName -SourceName $SnapshotName -Overwrite $true

Id      : d7e39f69-fefc-8578-5a9f-cc5b87981952
Name    : hyperv-csv-01-DATA-DEV
Source  : @{Id='73cc51e1-b7a7-6000-d3f1-f3e4b19fd030'; Name='hyperv-csv-01-DATA-PROD'}

Bring the CSV Back Online and Re-attach VHDXs

Invoke-Command -Session $HVSession -ScriptBlock {
    param($res)
    Start-ClusterResource -Name $res -Cluster (Get-Cluster).Name | Out-Null
    Write-Output "$res online"
} -ArgumentList $TargetCSVResource

Cluster Disk 11 online

Wait briefly for the CSV to mount, then re-attach the VHDXs.

Start-Sleep -Seconds 5

Invoke-Command -Session $HVSession -ScriptBlock {
    param($dataVhdx, $logVhdx, $dCN, $dCL, $lCN, $lCL, $vm)
    Add-VMHardDiskDrive -VMName $vm -Path $dataVhdx -ControllerType SCSI -ControllerNumber $dCN -ControllerLocation $dCL
    Add-VMHardDiskDrive -VMName $vm -Path $logVhdx  -ControllerType SCSI -ControllerNumber $lCN -ControllerLocation $lCL
} -ArgumentList $ClonedDataVhdx, $ClonedLogVhdx, $DataCtrlNum, $DataCtrlLoc, $LogCtrlNum, $LogCtrlLoc, $TargetVM

Restore the Database

With the snapshot restored files now in place on the correct paths, let’s restore. We use METADATA_ONLY to tell SQL Server the data is already physically present from the snapshot. There’s nothing to copy. REPLACE handles any stale catalog entry, and NORECOVERY leaves the database in RESTORING mode so we can apply the log backup.

Start-Sleep -Seconds 3

$Query = "RESTORE DATABASE [$DbName] FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE, NORECOVERY"
Invoke-DbaQuery -SqlInstance $SqlInstance2 -Database master -Query $Query -Verbose

VERBOSE: RESTORE DATABASE successfully processed 0 pages in 0.705 seconds (0.000 MB/sec).

Let’s verify the database is in RESTORING mode.

Get-DbaDbState -SqlInstance $SqlInstance2 -Database $DbName

Access       : MULTI_USER
ComputerName : hv-sql-02
DatabaseName : TPCC-4T
InstanceName : MSSQLSERVER
RW           : READ_WRITE
SqlInstance  : hv-sql-02
Status       : RESTORING

Apply the Log Backup

Now restore the log backup to bring the database forward.

$Query = "RESTORE LOG [$DbName] FROM DISK = '$($LogBackup.BackupPath)' WITH NORECOVERY"
Invoke-DbaQuery -SqlInstance $SqlInstance2 -Database master -Query $Query -Verbose

VERBOSE: Processed 8 pages for database 'TPCC-4T', file 'tpcc_log' on file 1.
VERBOSE: RESTORE LOG successfully processed 8 pages in 0.010 seconds (6.250 MB/sec).

Bring the Database Online

$Query = "RESTORE DATABASE [$DbName] WITH RECOVERY"
Invoke-DbaQuery -SqlInstance $SqlInstance2 -Database master -Query $Query

$RestoreStop = (Get-Date)
Write-Output "The restore time takes...$(($RestoreStop - $RestoreStart).TotalMilliseconds)ms!"

The restore time takes...21916.7855ms!

Let’s check the final state, we’re back online!

Get-DbaDbState -SqlInstance $SqlInstance2 -Database $DbName

Access       : MULTI_USER
ComputerName : hv-sql-02
DatabaseName : TPCC-4T
InstanceName : MSSQLSERVER
RW           : READ_WRITE
SqlInstance  : hv-sql-02
Status       : ONLINE

Get-DbaDatabase -SqlInstance $SqlInstance2 -Database $DbName |
    Select-Object Name, Status, SizeMB

Name     Status  SizeMB
----     ------  ------
TPCC-4T  Normal  4043579.50

TPCC-4T is online on SQL 02.

How Long Did the Backup and Restore Take?

Just to recap the timing. The $Start timer begins right before SUSPEND_FOR_SNAPSHOT_BACKUP = ON and stops right after BACKUP DATABASE ... WITH METADATA_ONLY completes. For this 4 TB database, the full freeze window runs about 4 seconds. Almost all of that is the metadata backup write to the UNC share. The FlashArray snapshot itself completes in milliseconds. I’ve gotten this as low as a few hundred milliseconds in my lab when writing to local disk and then copying the metadata file to a local share or to an object store.

Here’s the output from a run:

The snapshot time takes...4021ms!

That’s 4021ms for this run. Remember, the freeze is only for writes. Reads continue normally. Most applications can tolerate a write IO freeze of a few seconds, especially during a scheduled maintenance window. And this is certainly less impactful than a full backup on the host.

The $RestoreStart timer begins right after ALTER DATABASE SET OFFLINE and stops after RESTORE DATABASE ... WITH RECOVERY completes. It covers the full storage swap: VHDX detach, CSV offline, FlashArray clone, CSV online, VHDX reattach, and all three RESTORE steps. For a 4 TB database, that entire sequence, including all the Hyper-V cluster resource management, completes in about 22 seconds. No data was copied at any point.

The restore time takes...21916.7855ms!

That’s 21916.7855ms for this run. How long does it take you to perform a 4 TB database restore from a full backup? Hours?

Wrapping Things Up

The Hyper-V adds some additional layers around the storage operation: you’re managing cluster resources and VHDX attachments at the hypervisor level rather than simply onlining and offlining a disk. But the SQL Server story is exactly the same. Freeze with SUSPEND_FOR_SNAPSHOT_BACKUP, snapshot the FlashArray volume, write the metadata file with BACKUP DATABASE ... WITH METADATA_ONLY, and you have a full backup that completed in hundreds of milliseconds regardless of database size. Then combine it with a log backup for point-in-time recovery.

The key things to take away from this post:

  • Single-volume CSV — when data and log VHDXs share a single CSV volume, a volume snapshot captures both files consistently, no Protection Group needed
  • Instant restore — once the files are in place, RESTORE DATABASE ... WITH METADATA_ONLY, NORECOVERY puts the database in RESTORING mode in under a second, regardless of database size. No data copy, no waiting

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. Understanding I/O Freeze in T-SQL Snapshot Backups - Understand the I/O freeze mechanism that enables T-SQL Snapshot Backups and how SQL Server suspends write I/O to ensure consistent snapshots
  3. Using T-SQL Snapshot Backup - Point in Time Recovery - Learn how to perform point-in-time recovery using snapshot backups and transaction log backups
  4. Using T-SQL Snapshot Backup - Point in Time Recovery - Azure Edition - Implementing T-SQL Snapshot Backup techniques with Azure Virtual Disks
  5. Using T-SQL Snapshot Backup - Multi-Array Database Snapshots - Coordinating snapshots across multiple Pure Storage FlashArrays for distributed databases
  6. Using T-SQL Snapshot Backup - Seeding Availability Groups - Use snapshot backups to quickly seed new Availability Group replicas
  7. 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
  8. Building a Snapshot Backup Catalog - Create a queryable snapshot catalog using SQL Server 2025’s native REST API integration
  9. Building a Snapshot Backup Catalog - SQL Server 2022 Edition - Achieve the same snapshot catalog functionality using PowerShell with SQL Server 2022
  10. Using T-SQL Snapshot Backup - Hyper-V Edition (This Post) - T-SQL Snapshot Backup in a Hyper-V cluster environment with CSV-backed VHDXs on Pure Storage FlashArray