Handy DB Scripts – Last Database Restore Date

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s