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.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

4 Responses

  1. Adam Gorge says:

    Hello Manvendra,

    Thank you for sharing on DB restoring. But the result is not showing the last time restoration information and my problem is still remain same even I have run the command, Please resolve my issue.

  2. Anonymous says:

    my restorehistory table has no data

  3. Manvendra says:

    Adam,

    Can you share/elebarote your issue.I am not getting whihc type of issue you are getting? ideally when you run this cmd you will get last restore time of your dbs.

  4. Maruti Nandan says:

    You can check from
    msdb.dbo.restorehistory table

Leave a Reply

Your email address will not be published. Required fields are marked *