Find out when last bakcup was restored in SQL Server DB

Posted on Updated on

Sometime we may need to find out which date some perticular database was restored and by whom? Use following query to find out this information.

WITH LastRestores AS ( SELECT DatabaseName = [d].[name] , [d].[create_date] , [d].[compatibility_level] , [d].[collation_name] , r.*, RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) FROM master.sys.databases d LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name ) SELECT * FROM [LastRestores] WHERE [RowNum] = 1