when were databases last restored

    SELECT A.restore_history_id
         , A.restore_date  --date database was restored
         , A.destination_database_name
         , A.user_name
         , A.restore_type
         , B.backup_finish_date --date of backup used to restore database
         , C.physical_device_name  --backup file used to restore database
         , A.stop_at
         , A.stop_at_mark_name
         , A.stop_before
      FROM msdb.dbo.restorehistory A
 LEFT JOIN msdb.[dbo].[backupset] B
        ON A.backup_set_id = B.backup_set_id
 LEFT JOIN msdb.[dbo].[backupmediafamily] C
        ON B.[media_set_id] = C.[media_set_id]
     WHERE A.restore_date 
         = ( SELECT MAX(A1.restore_date)
               FROM msdb.dbo.restorehistory A1
              WHERE A1.destination_database_name = A.destination_database_name )