This script will list the location of the last Full, Differential and Tlog (if exists)
I use three CTE and at the end I use a UNION all
the last two select statements are joins so it displays NULL if either Diff or Tlog do not exist
/* DESCRIPTION List location of all backup for a single database. Includes if a Tlog or Dif exists or not CONFIGURATION Set @LastXday to limit the number of days to search for Set @dbname for the database Compatibility list: MSSQL2005 MSSQL2008 MSSQL2012 MSSQL2014 */ declare @dbname varchar(500) declare @LastXdays nvarchar(2) set @dbname = db_name() --SET @dbname = 'ELMO' set @LastXdays = '30' ;with fullbk as ( SELECT top 1 s.database_name , db_id(s.database_name) 'dbid' ,s.recovery_model ,m.physical_device_name ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken' ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days] ,s.backup_start_date ,'full' 'bk_type' ,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number' FROM msdb.dbo.backupset s inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name like @dbname and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays and s.[type] = 'D' ORDER BY s.backup_finish_date desc ) , Diffbk as ( SELECT top 1 s.database_name , db_id(s.database_name) 'dbid' ,s.recovery_model ,m.physical_device_name ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken' ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days] ,s.backup_start_date ,'Diff' 'bk_type' ,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number' FROM msdb.dbo.backupset s inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name like @dbname and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays and s.[type] = 'I' ORDER BY s.backup_finish_date desc ) , TlogBK as ( SELECT top 1 s.database_name , db_id(s.database_name) 'dbid' ,s.recovery_model ,m.physical_device_name ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken' ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days] ,s.backup_start_date ,'Tlog' 'bk_type' ,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number' FROM msdb.dbo.backupset s inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name like @dbname and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays and s.[type] = 'L' ORDER BY s.backup_finish_date desc ) select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.bk_type,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date from fullbk a where 1=1 and a.number = 1 UNION ALL select c.database_name, c.recovery_model, 'Diff', coalesce(b.physical_device_name,'') as 'physical_device_name', b.bk_type, b.seconds_TimeTaken, b.age_in_days, b.backup_start_date from Diffbk b right join fullbk c on b.dbid = c.dbid where 1=1 and c.number = 1 UNION ALL select c.database_name, c.recovery_model, 'tlog', case c.recovery_model when 'simple' then '' else b.physical_device_name end as 'physical_device_name', b.bk_type, b.seconds_TimeTaken, b.age_in_days, b.backup_start_date from Tlogbk b right join fullbk c on b.dbid = c.dbid where 1=1 and c.number = 1