The Issue - SQL Server Backup Sets and VSS Full-Backups

If you're using PVE and you are backing up your VMs with Proxmox, either "manually" or with Proxmox Backup Server (PBS), there are some catches that could end in data loss you might not have anticipated.

This is because when Proxmox uses the QEMU guest agent to run fsfreeze, it requests a full backup from the SQL VSS writer. That break the backup set for all databases because it consolidates / deletes the logs that are needed to restore. Here are some examples:

In this first example, the Proxmox backup runs directly after the Backup softwares backup - leading to all scheduled differential backups to fail.

In this second example, there will be a differential backup that runs without an error, but then won't be able to restore because the rollback/ rollforward log files won't exist anymore due to proxmox backup.

In the last example, having weekly full backups and daily incremental backups that get interrupted by a proxmox backup will result in four backups not being restorable.

You might get errors like "Invalid backup chain detected for database 'TEST'" or "Error 3041: Failed to create backup for database 'TEST'" when trying to run an incremental or differential backup.

So, even if you do not usually use Proxmox backups, but you need a backup for a specific case, or to test something - you run the risk to damage the backups of the MSSQL Databases.

Workaround: Just don't freeze the Filesystem -> No consistent Backups.

On your search for a resolution, you might (or might not) have read this passage on the pve wiki.

On Windows guests, some applications might handle consistent backups themselves by hooking into the Windows VSS (Volume Shadow Copy Service) layer, a fs-freeze then might interfere with that. For example, it has been observed that calling fs-freeze with some SQL Servers triggers VSS to call the SQL Writer VSS module in a mode that breaks the SQL Server backup chain for differential backups.

For such setups you can configure Proxmox VE to not issue a freeze-and-thaw cycle on backup by setting the freeze-fs-on-backup QGA option to 0. This can also be done via the GUI with the Freeze/thaw guest filesystems on backup for consistency option.

It even has a red warning sign "Disabling this option can potentially lead to backups with inconsistent filesystems and should therefore only be disabled _if you know what you are doing_."

That is because if you know what you are doing, you should not want to disable this option if at all possible.

Since this isn't really a Proxmox issue, the option to not freeze and thaw is really a "workaround" option and as ozdjh explained here, it "results in the same outcome as if the VM lost power and restarted at that point in time." - which is fine I guess, just not if you want consistent Backups (which you want, if at all possible).

Yes, MSSQL Server is very sturdy and robust, but if guaranteed consistency is required, this just isn't an option.

The solution - Telling the Qemu-Guest Agent it should use VSS_BT_COPY to backup this host.

Since a (short) while, the QEMU guest agent has an option to change the backup type - yet the information is hidden extremely well... (It's not really documented, except in the mailing list and the code...).

So here's what you need to do to create consistent Proxmox backups that don't break your regular MSSQL backups.

You need at least the guest agent that comes with the Virtio-Win version 0.1.262-1 installed. You can get the latest iso here.

Also: We're assuming your System is running on Proxmox >8.2 - all other setups are untested.

Once you installed that and rebooted your server, open the registry editor.

Important: You might need to re-set the IP Adress of your network adapter due to virtio replacing your network drivers. We recommend using static leases with your DHCP Server to solve this specific weirdness.

Navigate to "[Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\QEMU Guest Agent VSS Provider]"

You'll either see a REG_DWORD with the integer value 1 already present, then you can set it to 5, or if not, you need to create it.

Check your setup:

• Make sure, you have enabled the Qemu Guest Agent in the VM configuration.
• Make sure you have enabled "Freeze/thaw guest filesystems on backup for consistency"

Then, you need to restart your server again.

That's it - You now have consistent Backups for your MS SQL Databases, no matter what.

To compress this a bit:

1. Download latest VirtIO-Win driver disk and update qemu guest tools to at least 0.1.262-1
2. Restart your server. (And maybe fix networking issues due to driver updates)
3. Set REG_DWORD Registry Key "VssOptions" to the integer number 5 for VSS_BT_COPY here Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\QEMU Guest Agent VSS Provider
4. Make sure you have the Qemu guest Agent enabled, and you did not disable "Freeze/thaw guest filesystems on backup for consistency"
5. Reboot your server

Test your Backups

And finally - Make sure to test your backups by trying to restore them...

Disclaimer: The information provided in this blog post is for educational purposes only. While every effort has been made to ensure accuracy, neither DatACT GmbH nor the posts writer can be held liable for any errors or omissions, as well as any and all issues that may arise from following the suggestions or instructions herein. It is your responsibility to back up your data and make informed decisions about changes to your systems. By proceeding with any actions based on the information in this post, you agree that you do so at your own risk and that any resulting changes, damages, or losses to your systems are solely your responsibility.