Microsoft SQL Server backup physical_device_name

Question

I've configured 2 backup tasks in Microsoft SQL Server, to have a full and incremental backup of our database. The version of Microsoft SQL Server is 2008 R2.

The problem is, I've configured a location and until a couple of weeks ago everything went fine. The problem is that it currently creates those backups in a location that is not possible to find. If I check the logs, everything went ok. But in the target directory, there's no trace of that backup file.

When I execute the following query, I get a bizarre value in physical_device_name:

SELECT
    physical_device_name,
    backup_start_date,
    backup_finish_date,
    backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'DB_NAME'
ORDER BY backup_finish_date DESC

These are the values that I get for physical_device_name:

• {4CAE7525-44D7-4DEF-86A7-F9C7C99C013C}3
• {EC6FB844-832G-4A8F-BDDE-12D073383139}3

And so on ...

Any idea why this is and how to resolve it? My initial thought was that those directories are readonly. I changed that, but I saw that one of the backups last night failed again because of the same reason.

Answer 1

Good day,

SQL Server supports virtualization-aware backup solutions that use volume shadow copy (VSS) also named volume snapshots. For example, SQL Server supports Hyper-V and VMware backup. For more information check this document.

When the host backups your system the SQL Server VSS Writer service is used (It should be running when SQL Server is installed on virtual machine).

You should notice that these backups have value 7 in the column "device_type" (7 means Virtual device). These rows are actually very useful, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance. there's no trace of these backup files since they are above the scope of the virtual machine (above the level of your machine) - these are triggered by the host (Hyper-V or VMware for example).

More information here.