I recently set up an Azure VM and installed SQL Server 2017 – it worked great until it came time to set up the database maintenance plans. My normal routine is to set up a weekly full database backup, daily differential database backups, and hourly log backups. Thanks to Azure, I was able to send them to URL so they went directly to my BLOB storage container.
After setting up the backups, I tested them and everything went time – I ran the weekly full successfully, then the daily differential, then the logs, and didn’t get any errors. However, that night I got an email that the differential had failed, and the agent log had this note in it:
Code: 0xC002F210
Source: (Maintenance plan)
Execute SQL Task
Description: Executing the query "BACKUP DATABASE [MyDatabase] TO U…" failed with the following error: "Cannot perform a differential backup for database "MyDatabase", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I had tested everything earlier, so I wasn’t sure why the differential backup was failing now (the log backups were working without issue the whole time, so it wasn’t storage). I executed the differential again and it failed with the same error. I ran the full backup successfully and then tested the differential backup a few times – it now ran successfully each time. I shrugged and went on my way, chalking it up to something going wrong, only to have the differential backup fail again that night with the same error.
To see what was going on, I took a look at the backup sets and noticed some backups I hadn’t scheduled going a device I didn’t recognize (all of my scheduled backups were to URL, so they were easy to spot because they all start with “HTTPS://”):
select top 100 *
from msdb.dbo.backupmediafamily mf
where TRY_CONVERT(uniqueidentifier, physical_device_name) is not null
The GUID indicates a backup taken through the VSS service – in this case, they were being created externally by the Azure VM Snapshot process. However, these backups are by default official database backups, and they interrupt the database backup chain – had I attempted to restore one of my full database backups followed by the log backups, I would have found that I couldn’t restore past the VSS backup timestamp because I didn’t have access to that media.
The good news is that there’s a registry key you can use to tell the VSS service that database backups should be taken as COPY_ONLY (meaning they don’t interrupt your backup chain). You can do this with the following registry key:
Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BcdrAgent] "UseVSSCopyBackup"="True"
This tells the server that when VSS takes a snapshot, it should do it as a non-authoritative copy-only backup. This also means it won’t reset any attributes in the VM that say a backup has happened, but in my case, I didn’t want that to happen.
To learn more, check the Microsoft articles that provide details about snapshot backups for Azure VMs – neither refers to the error message specifically, but they provide some additional details about configuration. They’re https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-introduction#snapshot-creation and https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues (both linked to the most relevant section).
I hope this saves you the hours of aggravation that it cost me – let me know if it helps or if you have any issues!
