Check last database restore date/time using SQL Server

Use one of the following scripts.

The first script is quick and simple:

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = DB_NAME()
ORDER BY restore_date DESC

The second script also shows the backup file used during the restore:

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

Source: