How to get details of all newly restored databases from SQL server?

Here is the code to get all restored databases in SQL server.





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 


Just paste this code to query window and execute,. Its results the restored database list.
  

:)