Sql-server – Where is the SQLS database backup virtual device string stored in the system tables

backupsql server

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

Best Answer

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?

Related Question