With the system table in 'msdb', you can easily figure out the SQL server database last restore time.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform SQL related queries.
In this context, we shall look into how to perform Database Backup and the last restore time.

More about Last Restore Time of SQL server database ?

Database Backup and restores are common tasks for any database administrator. Similarly, we can also get information about the last restore time of the database.
There is a system table named 'restorehistory' in 'msdb' system database that captures this information. We need to gather information from this table to get the data.
We run the T-SQL code below to get this info:

use msdb go select Destination_database_name AS [DB Name],user_name AS [User] ,restore_date As [Last Restore Date] from restorehistory where Destination_database_name like (‘qa%’)

Once we run the above command, we will get output. Here, we can see the database name, a user that has restored the database, and the last restore date and time.

More about Restore history ?

Restorehistory system table is very informative in case we need information for audit or database forensics purposes.
This table contains the row for each database restoration performed:

SELECT

   [restore_date]

      ,[destination_database_name]

      ,[user_name]

      ,[backup_set_id]

      ,[restore_type]

      ,[replace]

      ,[recovery]

      ,[restart]

FROM [msdb].[dbo].[restorehistory]

We get the following database restoration history.
i. restore_date: It shows the database restoration date.
ii. destination_database_name: Here, we can get the destination database name using this column.
iii. user_name: Gives the user name that performed the restoration for that particular database.
iv. backup_set_id: We can join this column with backupset table to get information about the backup file.
v. restore_type: We can use this column to know the kind of database restoration performed on a particular database.
Where;
D – Database
I -Differential
L – Log
V – Verifyonly

vi. replace: once we execute a database restore command, we set this option to replace the existing destination database.
1 – Specified
0 – Not specified

vii. recovery: In the database restore query, we also specify the Recovery and Norecovery option.
1 – RECOVERY
0 – NoRecovery

viii. Restart: It shows whether the restore operation specified the RESTART option or not.
1-Specified
0-Not specified

ix. restorefile: We get the row for the restore file. We can join this table with restorehistory table on the restore_history_id column as well.
x. Destination_phys_name: It gives the name of the physical file with the complete path. We will get the detail of each physical file.
xi. restorefilegroup: We can do filegroup restore as well in SQL Server.

A FILEGROUP backup and restore allows restoring the objects related to a specific filegroup only. Generally, each database has a Primary filegroup that contains the primary data file MDF.

SELECT [restore_history_id]

,[filegroup_name]

FROM [msdb].[dbo].[restorefilegroup]

xii. [restore_history_id]: We can join this column with other MSDB tables to get more information.
xiii. Filegroup_name: It is the name of the FILEGROUP on which restoration was performed.

Let us fetch information from the MSDB using internal tables with the following query.
For instance, we join the restrehistory and restorefile tables with the backup history information tables to get complete information.

SELECT

rh.destination_database_name AS [Database],

CASE WHEN rh.restore_type = ‘D’ THEN ‘Database’

WHEN rh.restore_type = ‘F’ THEN ‘File’

WHEN rh.restore_type = ‘I’ THEN ‘Differential’

WHEN rh.restore_type = ‘L’ THEN ‘Log’

ELSE rh.restore_type

END AS [Restore Type],

rh.restore_date AS [Restore Date],

bmf.physical_device_name AS [Source],

rf.destination_phys_name AS [Restore File],

rh.user_name AS [Restored By]

FROM msdb.dbo.restorehistory rh

INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

ORDER BY rh.restore_history_id DESC

GO

[Need urgent help to find out the last restoration? We'd be happy to assist. ]