Location of very last Backups — All Databases

Backups, Reports, restore

One script with some CTE, but two ways to show the info.

A narrow view: 

 /*
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

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
--	top 10
	 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 1=1
--    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 10
	 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 1=1
--    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 10
	 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 1=1
--    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
)
, final as
(
select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,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' 'bk_type', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
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 and coalesce(b.number,1) = 1

UNION ALL

select c.database_name, c.recovery_model, 'tlog' 'bk_type', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
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 and coalesce(b.number,1) = 1
)
select * from final
order by 1,3

 

 

A wide view 


 /*
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

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
	 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 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
)
, Diffbk as (  
SELECT
	 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 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
)
, TlogBK as (
SELECT
	 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 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
)

select a.database_name, a.recovery_model
	, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
	, 'DIFF' 'DIFF_BK', b.physical_device_name,b.Seconds_TimeTaken,b.age_in_days, b.backup_start_date
	,  'TLOG' 'TLOG_BK', c.physical_device_name,c.Seconds_TimeTaken,c.age_in_days, c.backup_start_date
from fullbk a
left join Diffbk b on a.dbid = b.dbid
left join TlogBK c on a.dbid = c.dbid

where 1=1
and a.number  = 1 
and coalesce(b.number,1)=1
and coalesce(c.number,1)=1

union all
select Distinct
    convert(varchar(30),name) as DBname
    ,DATABASEPROPERTYEX(name,'recovery') 'RecoveryModel'
    ,'NO BACKUPS RECORDED ON MSDB',NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Leave a Reply

Your email address will not be published. Required fields are marked *