Skip to content

Restore History SQL by database name and type

by Dan Thompson on May 13th, 2013

;with restorehistory 
as (

	SELECT 
		 d.name [database_name]
		,case restore_type  
 			when 'D' then 'Database (Full)'
 			when 'I' then 'Differential database'
 			when 'L' then 'Log'
 			when 'F' then 'File or filegroup'
			when 'G' then 'Differential file'
			when 'P' then 'Partial'
			when 'Q' then 'Differential partial'
		 end as [restore_type]
		,restore_date
		,row_number() over (partition by destination_database_name,restore_type  order by restore_date desc) as restorerank
	FROM sys.databases d
	LEFT OUTER JOIN msdb.dbo.restorehistory rh on d.name = rh.destination_database_name
)

select 
	 [database_name]
	,[restore_type]
	,restore_date
from restorehistory
WHERE restorerank = 1 OR [restore_type] IS NULL
ORDER BY [database_name] ASC, [restore_date] DESC


From → T-SQL