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