text: /*
  Author: Tim Ford
  Original link: http://sqlmag.com/database-backup-and-recovery/how-old-are-your-backups
  */
  WITH full_backups AS (
  SELECT ROW_NUMBER() OVER(PARTITION BY BS.database_name,
                                        BS.type
                               ORDER BY BS.database_name ASC,
                                        BS.backup_finish_date DESC
                          ) AS [Row Number],
          D.name AS [database_name],
          BS.backup_set_id,
          BS.type AS backup_type,
          BS.backup_finish_date,
          D.recovery_model_desc
  FROM master.sys.databases AS D
          LEFT JOIN msdb.dbo.[backupset] AS BS
                  ON D.name = BS.database_name
  /* FILTERING OPTIONS*/
  --WHERE BS.[type] = '<backup_type,,D>'
  --WHERE BS.[name] = '<database_name,,Foo_DB>'
  )
  SELECT FB.database_name,
         CASE FB.backup_type
                 WHEN 'D' THEN 'Data'
                 WHEN 'I' THEN 'Differential'
                 WHEN 'L' THEN 'Transaction Log'
         END AS backup_type_desc,
         FB.recovery_model_desc,
         FB.backup_finish_date,
         BMF.physical_device_name,
         DATEDIFF(hour, FB.backup_finish_date, GETDATE()) AS backup_hours,
         DATEDIFF(minute, FB.backup_finish_date, GETDATE()) AS backup_minutes
  FROM full_backups FB
   LEFT JOIN msdb.dbo.[backupset] BS ON FB.backup_set_id = BS.backup_set_id
   LEFT JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
   WHERE FB.[Row Number] = 1
  ORDER BY FB.database_name, FB.[Row Number], FB.backup_type;
  