I recently came across a problem where a couple of our Azure SQL VMs (Yeah, I know.) were failing their differential backups because of a broken backup chain.
The order of backups was as follows:
- SQL full backup taken Sunday 8pm – Successful
- SQL differential backup taken Monday 8pm – Successful
- Azure VM backup taken Monday 9pm – Successful
- SQL differential backup taken Tuesday 8pm – Failed
As you can see from the pattern above as soon as the Azure full VM backup is taken after a SQL full backup any subsequent SQL differential backups will fail as the backup chain has been broken.
There are a number of ways to overcome this, one being to disable VM backups in Azure (don’t do that!) and another being to exclude SQL drives from the Azure VM backup, however the best solution is to set the following registry key in the VM:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BcdrAgent
USEVSSCOPYBACKUP = TRUE
After setting that registry key you fill find all your incremental backups work once more! 🙂