Script To Find Database Restore History In SQL Server

Basic Script to find Restore history:

Along with Restore history this script will also give you Source database , Server time taken in backup , user who did restore , and type of backup used for restore.

/**********************************************************/
Quick Download

SELECT

              [rs].[destination_database_name] as [Restored_database_name],

              [rs].[restore_date] as [Restore_Date],

              CASE

                       WHEN rs.restore_type = 'D' THEN 'Database'

                       WHEN rs.restore_type = 'F' THEN 'File'

                       WHEN rs.restore_type = 'G' THEN 'Filegroup'

                       WHEN rs.restore_type = 'I' THEN 'Differential'

                       WHEN rs.restore_type = 'L' THEN 'Log'

                       ELSE rs.restore_type

              END AS [Restore Type],

                     bs.server_name as backup_Source_Server,

                     [bs].[database_name] as [source_database_name],

                     [bmf].[physical_device_name] as [backup_file_used_for_restore] ,

                     [bs].[backup_start_date],

                     [bs].[backup_finish_date],

                     [rs].user_name

FROM msdb..restorehistory rs

INNER JOIN msdb..backupset bs

       ON [rs].[backup_set_id] = [bs].[backup_set_id]

INNER JOIN msdb..backupmediafamily bmf

       ON [bs].[media_set_id] = [bmf].[media_set_id]

Order by [rs].[restore_date]

 /**********************************************************/