Get Database Restore History in SQL

In SQL Server, you can track the restore history of databases by querying the msdb system database, which stores information about backups and restores. Here are some useful queries to get details about restore operations.

View Restore History for All Databases

You can use the following query to see the restore history of all databases:

 SELECT    
  [restore_date]   
   ,[destination_database_name]   
   ,[user_name]   
   ,[backup_set_id]   
   ,[restore_type]   
   ,[replace]   
   ,[recovery]   
   ,[restart]   
  FROM [msdb].[dbo].[restorehistory]   

View Restore History of a Specific Database of Database Server

To filter the results for a specific database, modify the query like this:

 SELECT    
  [restore_date]   
   ,[destination_database_name]   
   ,[user_name]   
   ,[backup_set_id]   
   ,[restore_type]   
   ,[replace]   
   ,[recovery]   
   ,[restart]   
  FROM [msdb].[dbo].[restorehistory]   
  WHERE [destination_database_name]='CRINOLD'  

View Details of the Last Restore Operation

To get details about the most recent restore operation for a specific database, use:

 SELECT TOP 1  
  [restore_date]   
   ,[destination_database_name]   
   ,[user_name]   
   ,[backup_set_id]   
   ,[restore_type]   
   ,[replace]   
   ,[recovery]   
   ,[restart]   
  FROM [msdb].[dbo].[restorehistory]   
  ORDER BY [restore_date] DESC  

Additional Information

To get more detailed information, you can also join with other tables in the msdb database, such as backupmediafamily or backupfile, depending on what information you are interested in.

Conclusion

These queries provide a solid foundation for retrieving restore history in SQL Server. If you have any specific requirements or need further assistance, feel free to ask!

Thanks