MSSQL server backup chain becomes broken after snapshot backup

Andrii Pavlikovskyi

Active Member
Sep 26, 2018
10
0
41
39
Proxmox VE 5.3
Windows Server 2016 VM (Qemu Agent enabled)
SQL Server 2016
Backup conf:
upload_2019-3-4_12-4-47.png

Inside Windows Server 2016 VM I have an app which runs MSSQL backups - a full backup once per week and differential backup once per day.

After Proxmox makes a snapshot, MSSQL backup chain becomes broken so I can't use differential backups. Tried to Google it but found nothing. Any suggestions?
 
In my scenario (with daily zfs-auto-snapshots) I'm also using additional Windows VM diffential backup tool for some data folders. Anyway seems that each differential backup takes pretty much same ammount of disk space as a regular "full" backup...

On the other hand making a data backup under VM (with atchive atributes update) also affects ZFS snapshot size.

Multiple layered backups seem to affect each other (@ least in some cases/scenarios) unless you figure it out precisely...
 
Proxmox VE 5.3
Windows Server 2016 VM (Qemu Agent enabled)
SQL Server 2016
Backup conf:
View attachment 9641

Inside Windows Server 2016 VM I have an app which runs MSSQL backups - a full backup once per week and differential backup once per day.

After Proxmox makes a snapshot, MSSQL backup chain becomes broken so I can't use differential backups. Tried to Google it but found nothing. Any suggestions?
The best way to create a fully consistent backup of workinkg sql server inside VM, use the command qm snapshot --vmstate 1 with enabled and running qemu agent inside a VM.
First: after command qm, agent will flush the buffers, before starting snapshot of disks (by command zfs snapshot create), and switch --vmstate will write RAM (with any not commited data). I think that, the GUI archiviser is doing snapshot with --vmstate 0, and this could be reason of your problems).
 
Try for testing other software for backup. "Duplicati" inside VM - it's also uses vss. You have to start this like a service from cmd, and before that, install vc_redist.x64.exe. Duplicati is doing diffirential backup od working databases, opened .pst outlook files succesfully in my VMs under the proxmox 6.0, and Proxmox makes snapshots with --vmstate 1 every two hours.
 
Try for testing other software for backup. "Duplicati" inside VM - it's also uses vss. You have to start this like a service from cmd, and before that, install vc_redist.x64.exe. Duplicati is doing diffirential backup od working databases, opened .pst outlook files succesfully in my VMs under the proxmox 6.0, and Proxmox makes snapshots with --vmstate 1 every two hours.
Thank you i'll try it
 
Попробуйте отключить службу «SQL Server VSS Writer» на гостевой виртуальной машине.

При резервном копировании виртуальной машины служба «SQL Server VSS Writer» запускается автоматически, и служба архивации виртуальной машины обращается к файлам данных SQL. Результатом является новая полная копия базы данных, полная копия базы данных разрывается в цепочке - дифференциальные копии базы данных, дифференциальные копии просто не видят новую полную копию базы данных, созданную путем архивирования виртуальной машины.

Если служба «SQL Server VSS Writer» не запущена, программы резервного копирования, работающие в Windows, не имеют доступа к файлам данных.

Прошу прощения за мой английский - это переводчик :(
Вот ссылка на статью с пояснениями от Microsoft. Только там про VMWare, но смысл такой же
 
Last edited:
  • Like
Reactions: Boris B.
Try for testing other software for backup. "Duplicati" inside VM - it's also uses vss. You have to start this like a service from cmd, and before that, install vc_redist.x64.exe. Duplicati is doing diffirential backup od working databases, opened .pst outlook files succesfully in my VMs under the proxmox 6.0, and Proxmox makes snapshots with --vmstate 1 every two hours.
try with mssql - it backup just files of db, this is not what i need. To make this work it is need to stop mssql server for backup time
 
Try disabling the "SQL Server VSS Writer" service in the guest virtual machine

When you back up a virtual machine, the "SQL Server VSS Writer" service starts automatically and the virtual machine archive service accesses the SQL data files. The result is a new full copy of the database, a complete copy of the database breaks in the chain - differential copies of the database, differential copies simply do not see a new full copy of the database made by archiving the virtual machine.

If the "SQL Server VSS Writer" service is not running, backup programs running on Windows do not have access to the data files.

I apologize for my English - this is a translator :(
Here is a link to an article with explanations from Microsoft (in Russian). Only there about VMVare, but the meaning is the same
Yes, i confirm, disabling service " SQL Server VSS Writer" is working and now backup chain not braked!
@rostma , thank you!
 

About

The Proxmox community has been around for many years and offers help and support for Proxmox VE, Proxmox Backup Server, and Proxmox Mail Gateway.
We think our community is one of the best thanks to people like you!

Get your subscription!

The Proxmox team works very hard to make sure you are running the best software and getting stable updates and security enhancements, as well as quick enterprise support. Tens of thousands of happy customers have a Proxmox subscription. Get yours easily in our online shop.

Buy now!