Database backed up to VIRTUAL_DEVICE GUID

Working with a customer who wanted to make sure their database backups were consistent before going live, we found the following entry in the log.


Database backed up. Database: ProductionDB, creation date(time): 2016/10/26(15:46:13), pages dumped: 70152, first LSN: 50966:4624:2, last LSN: 50966:13328:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9B4A021C-7A36-4358-B95B-3E93999DC1BD}8'}). This is an informational message only. No user action is required.

There were several things that we knew, first the SQL Server were running managed backups to Azure. A quick check of the storage account revealed the regular managed backups we expected to see.

Get-CloudBlob.ps1

We didn’t see any backups for the database when that log entry was created. We also knew that the server was being backed up by Azure Recovery Services, and this backup happened daily at 2am. This correlated with the stamp of the log entry, but we were unable to find where this backup was. We assumed that this had something to do with VSS and SQL so with this information we started searching for some folks experiencing similar issues and potential resolutions.

We found several articles that I’ll list below, but the general consensus was that backups in Windows will trigger VSS. The providers will then trigger the VSS writers to quiesce the SQL files. Nothing is being backed up per-se, but SQL is registering this is a backup event. There were several threads where the reported solution was to just disable the SQL VSS service, but I don’t think that’s wise.

To our thinking, if we disable the VSS writer for SQL, then when Azure attempts to perform a backup, it will have at the last an inconsistent backup, and at worst a corrupt backup as the files were in use at the time.

Knowing that the server itself is getting backed up, we launched the File Recovery (Preview) which is very slick. This launches from the portal, you select what date you want and it compiles a script that you download and run on the VM in Azure. This script will connect over iSCSI to the VHD and mount it for 12hrs letting you access the files on the server at that point in time. This would allow you to access the SQL Database files in their normal directories, albeit different driver letters. But they are not actual backups, it’s a snapshot of the data/files at that time.

While very cool, we needed to have a more reliable way to validate that our backups were vaild. Our concern was that the VSS triggered backups would break the restore chain, but turns out that our regular managed backups work as expected and we could restore the last full, and transact get us back to where we needed to be.

So, the thing to remember here is that if you see an entry like the above, keep calm. As long as you are doing your regular backups as you should, then everything will be ok.

And Happy National Backup Day!

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-backup-recovery

https://www.sqlservercentral.com/Forums/Topic666998-357-1.aspx

http://dba.stackexchange.com/questions/91812/where-is-the-sqls-database-backup-virtual-device-string-stored-in-the-system-tab

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c96bc256-99e5-453e-aa78-7910fe925f65/ntbackup-causes-sql-server-backups?forum=sqldatabaseengine

https://social.msdn.microsoft.com/Forums/en-US/e1ec1bc8-ee2b-4397-9b98-314d63e3e9a5/trying-to-find-out-whats-triggering-backups-and-where-they-are-going-to?forum=sqldatabaseengine

https://support.microsoft.com/en-us/help/956893/support-policy-for-microsoft-sql-server-products-that-are-running-in-a-hardware-virtualization-environment