I need to start collecting scripts someday…I’ve been saying that forever, but haven’t started yet. Maybe it’s time now, and this script is a good one to keep around.

This one is to find the last database restore date, and it looks something like:

WITH LastRestore AS

(

SELECT

     [name] AS DatabaseName

    ,[create_date] as CreateDate

    ,[compatibility_level] AS CompatLevel

    ,[collation_name] AS Collation

    ,[restore_date] AS LastDatabaseRestore

    ,ROW_NUMBER() OVER (PARTITION BY SysDb.Name ORDER BY [restore_date] DESC) AS RowNum

FROM master.sys.databases as SysDb

LEFT OUTER JOIN msdb.dbo.[restorehistory] as RestoreHist

     ON RestoreHist.[destination_database_name] = SysDb.Name

)

SELECT DatabaseName, CreateDate, CompatLevel, Collation, LastDatabaseRestore

FROM LastRestore

WHERE RowNum = 1

Thank you,

Lee