vwRestoreHistory

This JOINs the three available tables from msdb that have information on restore history.  One row per file.  If the database has one data and one log file, there will be two rows for this restore.

Use Admin

go

IF OBJECT_ID('dbo.vwRestoreHistory') Is Not Null

       DROP VIEW dbo.vwRestoreHistory

go

CREATE VIEW dbo.vwRestoreHistory

AS

/*     DATE          AUTHOR        REMARKS

       1/25/18       PPaiva        Initial creation.

       SELECT *

       FROM vwRestoreHistory

       ORDER BY ID desc

*/

SELECT  h.restore_history_id ID,

              restore_date RestoreDate,

              destination_database_name DestDB,

              fg.filegroup_name FileGroupName,

              f.destination_phys_name DestPathFile,

              user_name,

              backup_set_id,

              restore_type,

              replace,

              recovery,

              restart,

              stop_at,

              device_count,

              stop_at_mark_name,

              stop_before,

              h.restore_history_id

FROM msdb.dbo.restorehistory h

JOIN msdb.dbo.restorefilegroup fg

       ON fg.restore_history_id = h.restore_history_id

JOIN msdb.dbo.restorefile f

       ON f.restore_history_id = h.restore_history_id