Last 3 backups per database

last-backups.sql

WITH BackupSets AS (
      SELECT database_name
      ,            CASE [type]
                   WHEN 'D' THEN 'Full'
                   WHEN 'I' THEN 'Differential'
                   WHEN 'L' THEN 'Log'
                   WHEN 'F' THEN 'File/Filegroup'
                   END backup_type
      ,            backup_start_date, backup_finish_date, physical_device_name
      ,            ROW_NUMBER() OVER (PARTITION BY database_name, [type] ORDER BY backup_start_date DESC) as rn
      FROM  msdb.dbo.backupset AS B1
      JOIN  msdb.dbo.backupmediaset AS B2
        ON  B1.media_set_id = B2.media_set_id
      JOIN  msdb.dbo.backupmediafamily AS B3
        ON  B1.media_set_id = B3.media_set_id
      --WHERE physical_device_name NOT LIKE '{%'
)
SELECT database_name, backup_type
,            backup_start_date, backup_finish_date
,            CAST(backup_finish_date - backup_start_date AS time(0)) AS elapsed_time
,            physical_device_name
FROM  BackupSets
WHERE rn < 4
ORDER BY database_name, backup_start_date DESC