1

I created a maintenance plan to back up two user databases and system databases over a month ago. I queried the msdb database to view the size of the backups using the following query.

SELECT  
 bs.database_name,
 bs.type,
 (bs.backup_size/1024)/1024 [BackupSize (MB)],
 (bs.compressed_backup_size/1024)/1024 ,
 bs.recovery_model,             
 bf.physical_device_name,
 bs.user_name,
 bs.backup_start_date,
 bs.backup_finish_date 
FROM 
 msdb..backupset bs
INNER JOIN msdb..backupmediafamily bf
 ON bf.media_set_id=bs.media_set_id

What I saw was, I was able to view the backup set I had created with all the details along with the physical device name. However, there was one more backup set that had all the metrics regarding the backup size, when it was taken etc, but the physical device name had a hexadecimal representation. I verified the sql jobs and maintenance plans and found nothing related to this job. Where else should I find the source of this backup?

  • Aaron, your comment was not added – Info23 Jan 27 '15 at 14:37
  • Hmm, do you have any software that takes a VSS snapshot of the drives your databases are on? I'm wondering if, when the database is quiesced SQL registers it in msdb. – dbafromthecold Jan 27 '15 at 17:36
  • If you go to: Database Server -> Server Objects -> Backup Devices. Is there any relevant information about your backups there? – Chris L Jan 27 '15 at 23:11
  • I found out the device_type to be equal to 7, which means a virtual device. That explains why I was not able to find anything on the current server. – Info23 Jan 28 '15 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.