-----code:start-------------------------------------------------------------------
SELECT
DatabaseRestoredTo = RestHist.destination_database_name
,TimeOfRestore = RestHist.restore_date
,UserImplimentingRestore = RestHist.user_name
,RestoreType = CASE RestHist.restore_type
WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END
,ServerWhereBackupTaken = BackupSet.server_name
,UserWhoBackedUpTheDatabase = BackupSet.user_name
,BackupOfDatabase = BackupSet.database_name
,DateOfBackup = BackupSet.backup_start_date
,RestoredFromPath = MedFam.physical_device_name
FROM
msdb.dbo.restorehistory RestHist
INNER JOIN msdb.dbo.backupset BackupSet
ON RestHist.backup_set_id = BackupSet.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily MedFam
ON BackupSet.media_set_id = MedFam.media_set_id
ORDER BY
RestHist.restore_history_id
-----code:end-------------------------------------------------------------------
2. Example Results Messages
http://www.sqlservercentral.com/scripts/93052/
Article : What got restored from where, by who and when
By Anthony Green