SELECT [database_name]
,[recovery_model]
,[backup_start_date]
,[backup_finish_date]
,CASE [type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential DB Backup'
WHEN 'L' THEN 'Log Backup'
WHEN 'F' THEN 'File or Filegroup Backup'
WHEN 'G' THEN 'Differential File Backup'
WHEN 'P' THEN 'Partial Backup'
WHEN 'Q' THEN 'Differential Partial Backup'
END AS [Backup Type]
,[backup_size]
,[compressed_backup_size]
FROM [msdb].[dbo].[backupset]
ORDER BY [database_name], [backup_start_date]