How to get Last Restore Time of your database?

Database Backup and restores are common task for any database administrator.  We generally do lot of restore operations to fulfill user’s requirement. Such restores might be from production to lower life cycles or between the different landscapes like QA to DEV to test etc. We can easily get when was last time your database backed up using system table in msdb. Similarly, we can also get information about the last restore time of your database. Sometimes we need such information to figure out some issues.

There is a system table named restorehistory  in msdb system database that captures this information. We just need to gather information from this table to get such data. Run below T-SQL code 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 you will run above command, you will get below output. Here you can see the database name, user that has restored the database and last restore date and time. Restorehistory system table is very informative in case you need to some information for audit or database forensics purposes like who did restore and when.