4

I'm trying to disentangle our backup strategy, and also troubleshoot the overnight backup of the Azure VM and of the SQL DBs to local storage on the Azure VM, via an agent job and maintenance plan. The VM is only used for the SQL DBs and storing associated load files.

The managed backup of the VM starts at 11am and completes in 5-7 hours - this possibly seems too long?

The SQL DB backup job starts at midnight and is taking 4+ hours to backup DBs totalling 1.15TB and the log file shows repeated Error: 18210, Severity: 16, State: 1 and Error: 3041, Severity: 16, State: 1 errors.

From looking at the successful backup log entries, there seem to be two backups going on - one with type=disk and another with type=virtual device.

My questions are:

Is the Azure VM managed backup also backing up the SQL DBs? Is it likely that overlapping this with a SQL DB backup likely to cause a clash in some way?

Thanks!

SimonB
  • 51
  • 5

1 Answers1

6

Is the Azure VM managed backup also backing up the SQL DBs?

Yes it does takes "disk level backup" but not SQL Server specific backup which would mimic one taken by TSQL or Maintenance plan. If you refer to Online document Azure VM Backup it says

. By default, Azure Backup takes a full VSS backup (it truncates the logs of application such as SQL Server at the time of backup to get application level consistent backup). If you're using a SQL Server database on Azure VM backup, then you can modify the setting to take a VSS Copy backup (to preserve logs). For more information, see this article.

So it does above with SQL Server logs but do not take SQL Server specific backup.

Is it likely that overlapping this with a SQL DB backup likely to cause a clash in some way?

Yes it is, again if you read Troubleshoot VM snapshot issues it says that

VMs with SQL Server backup configured can cause snapshot task delay. By default, VM backup creates a VSS full backup on Windows VMs. VMs that run SQL Server, with SQL Server backup configured, can experience snapshot delays. If snapshot delays cause backup failures, set following registry key:

Solution: Make a Reg change as below

[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT]
"USEVSSCOPYBACKUP"="TRUE"
Shanky
  • 19,148
  • 4
  • 37
  • 58