Where is the SQLS database backup virtual device string stored in the system tables?

Question

In my SQLS Server 2005 log, when backups occur, I see something like: Database backed up. Database: blahblah, creation date(time): 2011/08/11(15:49:19), pages dumped: 1, first LSN: 142:489:1, last LSN: 142:491:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9A325509-4285-4C8A-E1B2-2F4815C4C4DF}15'}). This is an informational message only. No user action is required.

Regarding that virtual device specification, {9A325509-4285-4C8A-E1B2-2F4815C4C4DF}15'}:

In what system table is that virtual device string? If it matters, the reason I ask is I have a query regarding latest backups and need to filter based on the virtual device.

I have looked in all of the obvious places, like: msdb.dbo.backupmediaset msdb.dbo.backupmediafamily msdb.dbo.backupfile msdb.dbo.backupfilegroup sys.backup_devices

but no luck. If it is indeed stored somewhere, does the location change between SQL versions?

Any help appreciated.

TIA

dbaman

Answer 1

This is old, but I had the same issue. My SQL logs and backup tables were indicating that backups were happening, but even when I used the vssadmin command above, I could not locate the backup files. I listed the writers, but none matched the long string in the SQL logs, so I tried some of the other "vssadmin list" commands:

---- Commands Supported ----

List Providers - List registered volume shadow copy providers

List Shadows - List existing volume shadow copies

List ShadowStorage - List volume shadow copy storage associations

List Volumes - List volumes eligible for shadow copies

List Writers - List subscribed volume shadow copy writers

"List Providers" turned out to be the golden nugget. Although nothing was returned for existing copies or storage associations, the Providers listed the info I needed - it named my SAN provider. I looked in my storage tool and saw it was using Microsoft VSS, and the snapshot times correlated with what was in my SQL log.

Three things of note:

1. nowhere could I find anything to correlate to the TYPE=VIRTUAL_DEVICE: number listed in my SQL Logs (even though there were plenty of guids all over)

2. "List Shadows" did not return anything, which was scary until I looked at my storage tool and saw the snapshots there.

3. My DBs are all currently in "SIMPLE" recovery model, so I never have to do anything but attach database mdf/ldf files from the snap. This would be more complicated with databases that need to live in full recovery mode.

HTH future searchers - this has been bugging me forever. Where were my backups?

Answer 2

Again, this is old but for those that come here looking at this same issue on an SCCM SQL server it could be from the Configuration Manager site backup. The site backup asks for a drive on the SQL server and uses VSS to write the database backup there. This path does not show as the path if you query the backup locations, it only shows a guid looking number. Check with your SCCM admin about site backups.