SELECT TOP 100 mb.database_name AS [DatabaseName],
mb.recovery_model AS [Recovery],
mbm.physical_device_name AS [PhysicalName],
mb.backup_start_date AS [StartDate],
CASE mb.type WHEN 'D' THEN 'DataFile'
WHEN 'I' THEN 'DiffFile'
WHEN 'L' THEN 'LogFile' END [BackupType],
mb.first_lsn AS [LSN],
mb.user_name AS [UserName]
FROM msdb..backupset mb
INNER JOIN msdb..backupmediafamily mbm
ON mb.media_set_id = mbm.media_set_id
ORDER BY mb.backup_finish_date DESC
