---
title: VM level Snapshots & Differential Backups – What to keep in Mind
description: You want to protect your VM’s and databases in case of disaster. You implement a backup strategy, but they’re separate tech. What should you watch out for?
image: https://blog.coeo.com/hubfs/Coeo%20Socials-Jan-27-2022-11-24-35-75-AM.png
---

[![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com)

# VM level Snapshots & Differential Backups – What to keep in Mind

# The Coeo Blog

![Scott Ward](https://blog.coeo.com/hubfs/scott_small.png)

You want to protect your VM’s and databases in case of disaster. You implement a backup strategy to meet your requirements, but they’re separate technologies. What can go wrong? What should you watch out for? Let me tell you about something I came across recently…  

We were alerted to an issue with a client’s backups where the differential backup job had failed. Their backup routine was to perform a FULL backup for all databases once a week and take a differential at the same time for the remaining six days. We were alerted to the differential backup job failing midweek. The client’s SQL instance utilised Ola Hallengren’s maintenance solution and upon reviewing the job’s output logs, we could see that for all databases on the instance the differential backup could not be taken, returning the following error message: 

“Cannot perform a differential backup for database "DATABASE\_NAME", 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.” 

We then reviewed the backup history for one of these databases, using the following script: 

DECLARE @DbName sysname;

SET @DbName = 'DATABASE\_NAME';

 

SELECT 

       d.\[name\] AS \[Database name\],

       CASE b.\[type\] WHEN 'L' THEN 'LOG' WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' ELSE b.\[type\] END AS \[Type\],

       b.backup\_finish\_date AS \[Backup finish date\],

       CAST(DATEDIFF(minute, b.backup\_finish\_date, GETDATE()) / 60.0 AS decimal(10,1)) AS \[Backup age (hours)\],

       b.\[user\_name\] AS \[Username\],

       m.physical\_device\_name AS \[Backup device\],

          b.is\_copy\_only \[Is\_Copy\_Only\_Backup\]

FROM \[master\].sys.databases d

LEFT OUTER JOIN msdb.dbo.backupset b

       ON d.name COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = b.database\_name COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS

LEFT OUTER JOIN msdb.dbo.backupmediafamily AS m

       ON b.media\_set\_id = m.media\_set\_id

WHERE d.is\_in\_standby = 0                        /\* Not a log shipping target database \*/

AND d.state\_desc = 'ONLINE'

AND d.source\_database\_id IS NULL    /\* Excludes database snapshots \*/

AND d.\[state\] <> 1

AND b.\[type\] <> 'L' /\* exclude logs \*/

AND d.\[name\] = CASE WHEN @DbName = '' THEN DB\_NAME() ELSE @DbName END

ORDER BY \[Backup finish date\] DESC;

Interestingly, amongst the backups that were taken by the Ola Hallengren solution job, we could see that there were additional FULL backups which were not copy-only being taken to a GUID location, as below: 

![](https://blog.coeo.com/hubfs/undefined-Jan-27-2022-11-12-09-59-AM.png)

This type of GUID indicates that the backup was taken via VSS (Volume Shadow copy Service). SQL Server supports virtualisation-aware backup solutions that use VSS to take volume snapshots. Examples of such tools include, but is not limited to: 

- Hyper-V backup 
- Veeam 
- vRanger 

Our suspicions were confirmed when reviewing the SQL error logs where we could see messages around the time that these backups were taken, such as  

“*I/O is frozen on database DATABASE\_NAME. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.*” 

This, with the knowledge that this was an Azure VM, looked to be a VM snapshot. 

Snapshots taken at the VM level can potentially interrupt the differential bitmap and backup chain.  

In this case, a VM snapshot was taken mid-week on their Azure VM. This mid-week VM snapshot included the SQL instance and took a full backup of all databases. As these backups were not copy-only backups, this meant that any database recovery would require this backup to be utilised, thus breaking the differential backup chain set up as part of the existing Ola Hallengren backup solution. 

To rectify this issue, we needed to implement a registry edit for the server in question. This is as follows: 

\[HKEY\_LOCAL\_MACHINE\\SOFTWARE\\MICROSOFT\\BCDRAGENT\] 

"USEVSSCOPYBACKUP"="TRUE" 

This registry setting directs VSS to take full backups as copy-only as a part of its snapshots. This enables these VM snapshots to be taken without disrupting the backup chain. 

This is documented by Microsoft at the following link: 

[Troubleshoot backup errors with Azure VMs - Azure Backup | Microsoft Docs](https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues) 

After applying this registry edit, we could see that the backups were being taken by the SQL server backup jobs uninterrupted, and the now daily scheduled snapshots were taking copy-only backups each morning. 

![A screenshot of a computer

Description automatically generated](https://blog.coeo.com/hubfs/undefined-Jan-27-2022-11-12-29-42-AM.png)

As you can see, these technologies do not always play nicely together, but they can very easily be made to. We have given you the (registry) key to unlock this problem and avoid it in your own implementation… 

Have you encountered this issue? Got any questions? Let us know in the comments or contact us at info@coeo.com 

### Subscribe to Email Updates

## Related posts

---

### [Could verifying your backups be costing you money?](https://blog.coeo.com/verifying-backups-cost)

### [Recovering from Disaster - a five-point plan](https://blog.coeo.com/recovering-from-disaster-a-five-point-plan)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | info@coeo.com

## Contact Us

By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested.

You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**.

## Upcoming Events

[See all events](https://www.coeo.com/events/)

#### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB

[![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com)

- [Solutions](https://www.coeo.com/solutions/)
- [Next Steps](https://www.coeo.com/next-steps/)
- [Dedicated Support](https://www.coeo.com/dedicated-support/)
- [Case studies](https://www.coeo.com/case-studies/)
- [Technologies](https://www.coeo.com/solutions/technologies/)

- [Industries](https://www.coeo.com/industries/)
- [Finance](https://www.coeo.com/industries/finance/)
- [Retail](https://www.coeo.com/industries/retail/)
- [Technology](https://www.coeo.com/industries/technology/)

- [The Team](https://www.coeo.com/people/)
- [Join Us](https://www.coeo.com/careers/)
- [Graduate Programme](https://www.coeo.com/graduate-programme/)

- [About Coeo](https://www.coeo.com/about-coeo/)
- [The Coeo Blog](https://www.coeo.com/blog/)
- [Contact us](https://www.coeo.com/contact-us/)
- [Privacy Notice](https://www.coeo.com/privacy/)
- [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy)

- [Events](https://www.coeo.com/events)

Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png)

 Back to top