Get the information about the restored database(s)

Below script gives you the details (DB Name, Restored Name, Restore Data, Back up Start Date, Back up Finish date and Username) of all the restored databases. To get the details for specific database, just add the "where" clause with bckup.database_name=''

SELECT bckup.database_name AS 'DB Name' ,Restored_To_DBName AS 'Restored Name' ,Last_Date_Restored AS 'Restored Date' ,bckup.backup_start_date AS 'Backup Start Date' ,bckup.backup_finish_date AS 'Backup Finish Date' ,bckup.user_name AS 'User Name' FROM msdb..backupset bckup INNER JOIN (SELECT backup_set_id ,Restored_To_DBName ,Last_Date_Restored FROM msdb..restorehistory INNER JOIN ( SELECT rh.destination_database_name Restored_To_DBName , MAX(rh.restore_date) Last_Date_Restored FROM msdb..restorehistory rh GROUP BY rh.destination_database_name ) AS InnerRest ON destination_database_name = Restored_To_DBName AND restore_date = Last_Date_Restored ) AS RestData ON bckup.backup_set_id = RestData.backup_set_id