Script to find backup history , location , servername and backup size details

To find backup history details along with location we need to use backupset and backupmediafamily system tables available in msdb database.

These system tables are available from SQL Server 2000 version on wards so the below script will run from SQL Server 2000 and above versions.

SELECT TOP 5 a.server_name
 ,a.database_name
 ,backup_finish_date
 ,a.backup_size
 ,CASE a.[type] -- Let's decode the three main types of backup here
  WHEN 'D'
   THEN 'Full'
  WHEN 'I'
   THEN 'Differential'
  WHEN 'L'
   THEN 'Transaction Log'
  ELSE a.[type]
  END AS BackupType
 ,b.physical_device_name
FROM msdb.dbo.backupset a
INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name LIKE 'master%'
ORDER BY a.backup_finish_date DESC