1SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
2 b.database_name,
3 b.backup_start_date,
4 b.backup_finish_date,
5 b.expiration_date,
6 CASE b.type
7 WHEN 'D' THEN 'Database'
8 WHEN 'L' THEN 'Log'
9 END AS backup_type,
10 b.backup_size,
11 a.logical_device_name,
12 a.physical_device_name,
13 b.name,
14 b.description
15FROM msdb.dbo.backupmediafamily a
16INNER JOIN msdb.dbo.backupset b
17 ON a.media_set_id = b.media_set_id
18ORDER BY b.database_name,
19 b.backup_finish_date DESC;