SQL Server on Azure VM. Cannot Perform a Differential Backup for Database “xyz”, Because a Current Database Backup Does not Exist.

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.