Differential SQL Server Backup Fails on VM

If you are using Altaro VM Backup* as part of your data protection scheme, here’s a quirky thing you may run into if you also rely on SQL Server differential backups.

We needed a copy of one of our DBs from Feb 4. We have Full backups once a week and then Differentials done all the other days. I restored the full from Feb 3 (with NORECOVERY) but kept getting This differential backup cannot be restored because the database has not been restored to the correct earlier state. This usually means you’re using the wrong full restore, but in this case I wasn’t. There was my FULL done on the 3rd and the DIFF done on the 4th. No manual backups were done**.

Except I that the DIFF is based on the last full, not necessarily the one you’re expecting from your maintenance plan. I used a query to get the backup details.

SELECT 
msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.database_backup_lsn,
msdb.dbo.backupset.differential_base_lsn, 
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Diff'
END AS backup_type,
msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id

Which produces this result.

I was trying to restore the DIFF backup ending with 8847 to FULL ending with 4459..BUT…there’s a FULL with a GUID between “my” (maintenance plan) FULL and the DIFF. The GUID is the last full BU before the diff was taken. So the DIFF can’t be used because you can’t restore using the FULL one with the GUID.

Turns out that GUID is a VSS snapshot initiated by Altaro when the recommended Application Consistent BackUp is enabled. For SQL server ACBUs should be enabled or the backup will miss any uncommitted transaction logs. (they get rolled up every time a full backup is done)

If you search for this error, there’s a registry fix for a similar issue with Azure SQL Server backups. I’ve seen to add a “CopyOnly” key which is:

REG ADD "HKLM\SOFTWARE\Microsoft\BcdrAgent" /v USEVSSCOPYBACKUP /t REG_SZ /d TRUE /f

I checked with Altaro support chat and they had no information on whether it worked but I confirmed this morning it does NOT work for Altaro backups. I ran a FULL > Altaro > DIFF with the reg key created on a test VM. Same problem. The GUID FULL is between the FULL I want to apply the DIFF to. I let them know so now they do have it logged.

I thought about adjusting scheduling so the DIFF takes place before the Altaro backup but this doesn’t help because the Altaro creates a new FULL every night, so there’s no hope of using the DIFF without creating a Maintenance Plan FULL before each DIFF, which defeats the purpose. I’m removing the DIFF backups from my SQL Server maintenance plans. As you’ll see, I can still get the data I need without them, if a little less conveniently. But TBH, this is maybe a once every couple of years occurrence (for us…first time since we’ve used Altaro which is now 5 years)

So how did I get to the data I needed?

I restored a clone of the VM from the backup of the production SQL Server taken on Feb. 4th. Altaro restores clones with virtual switches disabled so there will be no network IP conflicts. After connecting to the clone via HyperV Manager, I fired up SSMS and did a manual backup. Even though this is a disposable VM, I used WITH COPY_ONLY because it’s just a good habit to get into. The backup file was 19GB.

So how to get that off the VM that has no network connectivity other than RDP through HyperV Manager? I tried using an Enhanced HyperV session to enable the clipboard for the session, but that didn’t work. Maybe the file was too large? I didn’t mess around with it (It was my day off)

What I did was shut down the clone VM, created a new 20GB VHDX and attached it to the clone. I then started up the VM and connected to it, then used Computer Management > Disk Management to initialize and bring the new virtual disk online for the OS. I then copied the backup file to the new VHDX, shut down the clone and removed the VHDX from the VM settings. I then attached the VHDX with the backup data to a VM that -was- on the network, brought it online and just copied the file to the production SQL Server to run the restore. (to a test database). I then deleted the backup file and shut down that VM and removed the VHDX from it’s settings. I decided to keep the VHDX and called it 20GBVirtualExternalHD because it basically operated as a virtual USB drive (although not quite as convenient). I then deleted the VM clone from HyperV and then its VHDXs. You’ll want to make sure you’re deleting the right ones.😉

Maybe an edge case, but then almost everything I post here is. Hopes it helps someone.

*I highly recommend Altaro VM Backup. It’s saved my butt on numerous occasions. It’s reasonably inexpensive as backup solutions go (~$100/VM per year), has the best chat based support I have ever encountered, has the ability to restore full VMs, VM clones, instantly mount VM backups and do granular file restores from backup VMs. It also has the ability to do 2 offsite backups. I backup to our satellite office and to Wasabi Cloud Storage (also highly recommend) for full 3-2-1 backup coverage.

**Note: this issue is why you need to be careful about manual backups. You can break the restore chain by doing a manual backup if they are for things like test DBs. Be sure to use WITH COPY_ONLY unless the backup is part of your live data.