When you’re a DBA, a problem that appears to be insignificant based on the error description may not be so when you probe further.
Our monitoring system just warned us that the ‘XYZ’ database was missing a differential backup, with an error message as follows:
“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]”
The aforementioned error statement leads to the conclusion that database ‘XYZ’ is new and does not have a first complete backup, which is required to take differential backups.
Please be aware that SQL was running on an AZURE VM. We looked into the database backups and discovered that complete backups were taking place every Sunday at 10 p.m., and daily differential backups were also taking place without issue until the day before we received this alert.
What changed since the differential backup was successful the day before?
We looked through the SQL error log and discovered a few intriguing information.
This, I remembered, happened when a VM level snapshot is taken. After additional investigation, we discovered that VM Snapshots had been taken as a failsafe. Please keep in mind that VM backup also takes full backups of SQL servers, which can be verified by looking at the backup history. SQL Server, on the other hand, does not rely on VM initiated full backups to trigger differential backups.
We had to come up with a technique to have differential happen effectively with VM snapshot because VM backup was a requirement for the customer.
We looked through the Microsoft support documents and discovered that there is a registry setting that can resolve this problem.
[HKEY LOCAL MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT] “USEVSSCOPYBACKUP”=”TRUE”
This registry option instructs VM snapshot to make a copy-only backup of the entire VM. This allows VM snapshots to take place without interfering with the backup chain.
The differential backup job was successfully finished when the registry was updated.
Refer: https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot
Please let us know if you find it intriguing or if you’ve ever seen a strange example of backup failure in the comments section.
Saurabh Srivastava's SQL Server Blog 