MSSQL server backup chain becomes broken after snapshot backup

[Andrii Pavlikovskyi]

Proxmox VE 5.3
Windows Server 2016 VM (Qemu Agent enabled)
SQL Server 2016
Backup conf:


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?

 

[spirit]

the agent use vss when proxmox make snapshot. Maybe this is broke mssql backup.

 

[jaceqp]

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...

 

[Andrii Pavlikovskyi]

the agent use vss when proxmox make snapshot. Maybe this is broke mssql backup.

I think so too. But how to handle it? I have no problems with VMware ESXi + Veeam in the same scenario.

 

[Andrii Pavlikovskyi]

Nobody knows?

 

[WOJCIECH]

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).

 

[spirit]

I think the problem is that the agent is using vss feature of windows to flush buffer, but this feature (vss) is also used by sqlserver for his own backup.

 

[Boris B.]

Hello. I'm getting this problem to. Do you have any solution?

 

[WOJCIECH]

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.

 

[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.

Thank you i'll try it

 

[rostma]

Попробуйте отключить службу «SQL Server VSS Writer» на гостевой виртуальной машине.

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

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

Прошу прощения за мой английский - это переводчик
Вот ссылка на статью с пояснениями от Microsoft. Только там про VMWare, но смысл такой же

 

[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

 

[Boris B.]

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!