About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Thursday, January 25, 2018

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

No comments:

Post a Comment