Find out when last bakcup was restored in SQL Server DB

Posted on May 6, 2019 Updated on May 6, 2019

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