How to identify when a SQL Server database was restored

To know when a database is restored run the below query in the database.

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Meanings of the column

destination_database_name  -   The name of the database that has been restored.
restore_date   -    The time at which the restore command was started.
backup_start_date  -  The time at which the backup command was started.
backup_finish_date  -  The time at which the backup command completed.
source_database_name  -  The name of the database after it was restored.
backup_file_used_for_restore  -  The file(s) that the restore used in the RESTORE command.