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.
- Understanding SQL Server Recovery Models
- Understanding SQL Server Backups
- How to know database version or build no from a backup file?
- What is Point-in-Time Recovery?
- Understanding SQL Server Backup Preferences for AOAG databases
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018