How to view database restore history

Owned by Knowledge Assistant
Feb 29, 2024
1 min read
Description of Issue

How can I see when a database was restored and what it was restored from?

Context

  • SQL Server

  • SQL Server Management Studio

  • SSMS

  • On-premise

Cause

If you are unsure of when a database was backed up or what source database was used in the restore process. 

Resolution

Run the following SQL script from SQL Server Management Studio (SSMS)

  1. Open SSMS

  2. Click on New Query

  3. Execute the following script:

    SELECT [rs].[destination_database_name],  [rs].[restore_date],  [bs].[backup_start_date],  [bs].[backup_finish_date],  [bs].[database_name] as [source_database_name], [bs].[server_name] as [source_server_name]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
ORDER BY [rs].[restore_date] DESC 


Additional Information