When you are a DBA, sometimes a problem which might seem so trivial from the error description may not really be so when you dig deeper.
Recently we got alerted from our monitoring system that ‘XYZ’ database was missing a differential backup with error statement as
“Cannot perform a differential backup for database “XYZ”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000]”
Initial thought that comes from above error statement is that database ‘XYZ’ is new and does not have a first full backup required to take differential backup.
Please note SQL was on AZURE VM. We checked the database backups configured and found that full backup happened every Sunday at 10 pm and daily differential backups were also happening without any issues till a day before got this alert.
Differential backup was successful a day before , So what changed?
We checked SQL error log and found few interesting tidbits there
I recalled this happens when a VM level snapshot occurs. On checking further, Customer informed us that VM Snapshots were taken as a failsafe. Please note that VM backup also takes SQL server full backups which could be verified by seeing backup history. But SQL server does not rely on these full backups to trigger differential backups.
Since VM backup was a must for customer, we had to come up with a way to have differential happening successfully with VM snapshot.
.We checked Microsoft support documents and saw that there is a registry setting that can fix this issue
[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT] “USEVSSCOPYBACKUP”=”TRUE”
This registry setting directs VM snapshot to take full backup as copy only. This helps VM snapshot to happen without disrupting Backup chain.
On updating the registry differential backup job completed successfully.
Refer:
https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot
Please let us know in comments if you find it interesting or faced any such curious case of backup failure.
Happy Learning !