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

  • Are you using a third party tool to backup your database? I do native backups so I don't have any examples of that to compare, but if you check system view sys.backup_devices, you might get more info. – Your_comment_is_not_funny Feb 12 '15 at 20:02
  • I am using Veritas Netbackup for official production backups, and a SAN snapshot-based backup thing for DR purposes. The virtual device will tell me which one did the backup. sys.backup_devices is empty... – Allen Feb 12 '15 at 20:15
  • 2
    That GUID should be the GUID of the SQL Writer VSS or another VSS provider. You won't find any information on it inside of SQL Server. Check out the vss command "vssadmin list writers" – Sean Gallardy Feb 12 '15 at 22:05

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?

  • That's a shame - list providers returns nothing for me and I still have no idea what's creating my mysterious backups... :( – NickG Dec 9 '15 at 14:09

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.