Hey Jacob, great question—this is such a common pain point when dealing with Windows VMs running databases in Proxmox, since the QEMU guest agent’s fsfreeze hook functionality doesn’t translate directly to Windows like it does for Linux. Let’s walk through practical, reliable solutions to get consistent snapshot backups without disrupting access to your MSSQL Express instance:

方案1：用Proxmox的guest-exec配合自定义PowerShell脚本 #

Since Windows doesn’t support the fsfreeze hook, we can leverage Proxmox’s ability to run commands directly on the guest VM via the QEMU agent to trigger MSSQL-safe snapshot prep. Here’s how to set it up:

• Step 1: Write the Windows-side PowerShell scripts
  Create two scripts on your Windows VM (e.g., in C:\Proxmox-Snapshot-Hooks\):

  1. Pre-Snapshot-MSSQL.ps1 (puts MSSQL in a safe state):

     # Replace [YourDatabaseName] with your actual DB name
     Invoke-SqlCmd -Query "ALTER DATABASE [YourDatabaseName] SET SUSPEND_FOR_SNAPSHOT = ON;" -ServerInstance ".\SQLEXPRESS"
     # Give MSSQL a moment to complete the state change
     Start-Sleep -Seconds 3
     

  2. Post-Snapshot-MSSQL.ps1 (restores normal operation after snapshot):

     Invoke-SqlCmd -Query "ALTER DATABASE [YourDatabaseName] SET SUSPEND_FOR_SNAPSHOT = OFF;" -ServerInstance ".\SQLEXPRESS"
     

  Note: Make sure the account running these scripts has db_owner permissions on your MSSQL database. Using the Local System account works well here, as it typically has the necessary access.

• Step 2: Add Proxmox host-side hook scripts
  On your Proxmox host, create two shell scripts (e.g., in /root/proxmox-hooks/):

  1. pre-snapshot-hook.sh (runs before the snapshot starts):

     #!/bin/bash
     # Replace 100 with your Windows VM's ID
     VMID=100
     # Execute the pre-snapshot script on the Windows guest
     qm guest exec $VMID -- powershell -ExecutionPolicy Bypass -File "C:\Proxmox-Snapshot-Hooks\Pre-Snapshot-MSSQL.ps1"
     # Wait for the SQL state change to finish
     sleep 5
     

  2. post-snapshot-hook.sh (runs after the snapshot completes):

     #!/bin/bash
     VMID=100
     qm guest exec $VMID -- powershell -ExecutionPolicy Bypass -File "C:\Proxmox-Snapshot-Hooks\Post-Snapshot-MSSQL.ps1"
     

  Don’t forget to make these scripts executable with chmod +x /root/proxmox-hooks/*.sh.

• Step 3: Attach hooks to your backup/snapshot task
  In the Proxmox Web UI, edit your scheduled snapshot backup task. Look for the "Pre hook script" and "Post hook script" fields, then enter the full paths to your shell scripts (e.g., /root/proxmox-hooks/pre-snapshot-hook.sh).

方案2：利用Windows VSS（卷影复制服务）自动处理一致性 #

This is the simpler, more "set-it-and-forget-it" option if your setup supports it:

• Windows’ VSS is natively integrated with the QEMU guest agent (as long as you have the latest version installed on the VM). MSSQL Express is VSS-aware, meaning it will automatically quiesce writes and ensure consistency when a VSS snapshot is triggered.
• To use this:
  1. Verify the Volume Shadow Copy service is running on your Windows VM (check Services.msc).
  2. When creating your Proxmox snapshot/backup task, select the snapshot mode (not stop or freeze) and ensure the "Use guest agent" checkbox is ticked.
• Pro tip: Test this first by manually creating a snapshot, then restoring it to a test VM to confirm the database mounts and runs without corruption.

方案3：结合MSSQL差异备份 + Proxmox快照 #

If you want an extra layer of safety, you can pair Proxmox snapshots with automated MSSQL differential backups:

• Add a step to your pre-hook script that runs a differential backup of your database to a local disk on the Windows VM:

  sqlcmd -S .\SQLEXPRESS -Q "BACKUP DATABASE [YourDatabaseName] TO DISK = 'C:\MSSQL-Backups\YourDB-Latest-Diff.bak' WITH DIFFERENTIAL;"
  

• This way, if the snapshot ever has consistency issues, you can restore the snapshot and apply the differential backup to get back to a fully consistent state.

关键注意事项 #

• Test everything first: Always validate your setup in a non-production environment. Restore snapshots to confirm your database boots up without errors.
• Permissions matter: Ensure the Proxmox user running the hook scripts has permission to execute qm guest exec, and the Windows account running the PowerShell scripts has MSSQL access.
• Log for troubleshooting: Add logging to your scripts (e.g., write to Windows Event Viewer in PowerShell, or /var/log/proxmox/ on the host) to make debugging easier if something goes wrong.

备注：内容来源于stack exchange，提问作者Jacob Jewett