Before-snapshot script hook for Windows guest on Proxmox?

I have a Proxmox server setup with a Windows 10 guest that will be running MSSQL Express. The Windows guest has QEMU guest agent installed and working. For backups, I have a separate server running Proxmox Backup, which is already scheduled to take full backups of the guest once a week (stop mode). In addition, I need to have more frequent snapshot backups with little to no access interruption, however, simply "freezing" the filesystem with an active database is problematic due to the chance of data inconsistency.

If this was MySQL on a Linux guest, there is already a solution in the form of adding a script to fsfreeze-hook, to the point that there is an example included in the repo. But, this entire feature seems to be missing on the Windows build of QEMU guest agent, from what little specific information I could find.

How can I achieve a similar guest hook script on Windows? Ideally, the script would be called right before the snapshot begins in order to put the database in a safe state for a snapshot to be taken.

You should be able to script a snapshot on a Windows machine using vssadmin create shadow. However, please note that:

• vss snapshots on Windows 10 have the reputation of being unusable due to snapshot data corruption (I had first-hand experience on the issue when trying to recover some snapshotted files via ShadowExplorer)

• any modern database engine (read: almost anything expect MyISAM) uses jornaling and/or WAL to survive a powerloss or a crash-consistent backup just fine.

In addition to taking whole-VM backups, I would suggest doing regular, scripted database dumps on other locations (ie: a network share) if possible.

I understand the requirements here, but unfortunately it is not easy for "alternative" virtualization platforms to deal with Microsoft's shenanigans. If you think that even Citrix gave up on quiescent snapshots in xenserver, you understand the challenge here.

I guess you could try calling vssadmin create shadow via ssh or powershell, but that I have never tried actually.

The only ways I know to reliably maintain backups of a Windows VM at an enterprise level with something like Proxmox are:

1. Since we are talking specifically a SQL Server machine, you keep the data separated because you backup with SQL Server's own functionalities. The OS you can use whatever, or just keep a spare/clone SQL Server installation (no license violation if it's stopped). However, you are using SQL Server Express, which is troublesome because you cannot have scheduled tasks, so you need to find something else.
2. Using Windows (Server) Backup from inside the VM, maybe coupled with a ZFS shared folder that you snapshot after the backup is taken, so that you can actually control the history you want to keep from the storage management side; this is free and actually works pretty well; recovery is not the fastest, as you need to boot the Windows installation iso and perform a fake "bare metal" restore inside the VM. You need to start the SQL Server VSS writer, and that should give you consistent backups of SQL Server, too, in theory. I do this regularly on development and test environments, but production is a different beast, and documentation is not the clearest (thanks Microsoft, good job as usual).
3. Use other certified backup solutions that do better job than Windows Backup, but again from inside the VM; still costly if you have to buy licenses for a lot of VMs, so at this point I would consider going for the big guys.

In fact, I know you are asking about Proxmox, but it must be said that professionally, the best way to manage Windows machines that bear important data is using VMWare vSphere or Hyper-V with Veeam Backup; it is the most costly solution, but also the coolest for disaster recovery: you can run the machine from backups, for example, and other cool features. I am not advertising anything; I have experienced pretty much every virtualization platform that makes sense, and if you go Windows, that's how it is.

Now, for the unreliable ways to do SQL Server backups under Proxmox specifically, I am amending my previous answer to comply exactly with what you are asking. I only want to underline this is not a best practice whatsoever, but I have seen that done even with VMWare (that's stupid).

What you could do is:

• install the OpenSSH server in Windows, which now Windows has

• set up hooks in proxmox backup (but you need to run backups from the command line, not from the web GUI) as described here: https://pve.proxmox.com/pve-docs/pve-admin-guide.html#_hook_scripts; this way, I can run a script that executes something inside the Windows VM via SSH

• that script I will run is not VSSADMIN but something like:

  SqlCmd -E -S LOCALHOST -Q "BACKUP DATABASE <DATABASENAME> TO DISK ='<PATH>'" or it can also be something more complicated, like incrementals backups and such. You can also install the scripts by this guy to handle backups better, if you are into SQL scripts: https://ola.hallengren.com/sql-server-backup.html.

• At this point you will backup the backup with Proxmox. That's garanteed to be consistent, if you wait for the buffers to be flushed (that's one reason it cannot be regarded as reliable from a professional stand point.)

However, the reasons I don't like this and the previous suggestion of doing VSS snapshots are:

• It seems overly complicated and you still are doing something that is unreliable
• In fact, if I have to rely on the SQL Server VSS writer service (that's mandatory otherwise no consistency whatsoever), I would revert to my previous suggestion of using Windows Backup + storage snapshots, which is all round better to manage than just calling VSS with a script and backupping the backup.

For my DB machines under Proxmox, XCP-ng or whatever, I have never bothered backing them up as machines; you just need to ensure they only contain the DB server, as it should be in general if it is some critical application. I think backing up those machines is waste of time. I always backup to an external storage (with scripts, scheduled tasks, mysqldump, SQL Server Agent, sqlcmd) because I can be up again in minutes with a docker container/VM I can prepare in advance if I really need to do disaster recovery. It also occupies a lot less space.