How do we collect SQL Server backup details from the system tables?

How do we collect SQL Server backup details from the system tables?

A customer told me yesterday that no SQL Server database backups were running.  Yet when I queried msdb..backupset, I could see backups were being recorded every day. There weren't any Agent jobs, so I wasn't sure exactly how the backups were being performed, or even where they were being written.

I threw this logic together to return all of the backup details to me, to include name of the software that is creating the backup. When we ran it for the customer, we found the backups were being run with Dell's AppAsure backup software.

/* 
Returns backup details for every database on the targeted instance.  To include - 
   BkupSoftware
   Server
   Database
   CompatibilityLevel
   RecoveryModel
   BackupType
   BackupStart/Finish Dates
   PhysicalDevice
   DeviceType
   BackupSize(Bytes)
   CompressedBackupSize(Bytes) - (Same as BackupSize(Bytes) if you are not compressing.)    */

USE msdb; 
SELECT 
    bms.software_name [BkupSoftware],
    bs.server_name [Server],
    bs.database_name [Database],
    CASE bs.compatibility_level 
         WHEN 80 THEN 'SQL v2000'
         WHEN 90 THEN 'SQL v2005 '
         WHEN 100 THEN 'SQL v2008' 
         WHEN 110 THEN 'SQL v2012'
    END AS CompatibilityLevel, 
    recovery_model [RecoveryModel],
    CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup' END [BackupType],
    bs.backup_start_date  [BackupstartDate],
    bs.backup_finish_date    [BackupFinishDate],
    bmf.physical_device_name [PhysicalDevice],
    CASE device_type 
         WHEN 2 THEN 'Disk - Temporary' 
         WHEN 102 THEN 'Disk - Permanent' 
         WHEN 5 THEN 'Tape - Temporary'
         WHEN 105 THEN 'Tape - Temporary' 
         ELSE 'Other Device' END [DeviceType],
    bs.backup_size [BackupSize(Bytes)], 
    compressed_backup_size [ConmpressedBackupSize(Bytes)] 
FROM
msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON ( bs.media_set_id = bmf.media_set_id ) INNER JOIN msdb.dbo.backupmediaset bms
       ON bs.media_set_id = bms.media_family_count
GROUP BY
    bms.software_name,
    bs.server_name,
    bs.database_name,
    CASE bs.compatibility_level
         WHEN 80 THEN 'SQL v2000' 
         WHEN 90 THEN 'SQL v2005 '
         WHEN 100 THEN 'SQL v2008'
         WHEN 110 THEN 'SQL v2012' 
    END,
    recovery_model,
    CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup' END,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name,
    CASE device_type
         WHEN 2 THEN 'Disk - Temporary' 
         WHEN 102 THEN 'Disk - Permanent' 
         WHEN 5 THEN 'Tape - Temporary'
         WHEN 105 THEN 'Tape - Temporary' 
         ELSE 'Other Device' END,
    bs.backup_size,
    compressed_backup_size
ORDER BY 
bs.backup_start_date DESC 

Take a look at each of these references to see more detail for each of these backup objects located within the msdb:

    backupset - http://technet.microsoft.com/en-us/library/aa260602(v=sql.80).aspx
    backupmediafamily - http://msdn.microsoft.com/en-us/library/ms190284.aspx
    backupmediaset - http://msdn.microsoft.com/en-us/library/ms189513.aspx