This article will review how to get information on your SQL database restore history, including the metadata in MSDB that can be queried, as well as value added tools and features to the group, sort, report and export this critical information.
DBA’s are responsible for maintaining the database backup and restoring activities. Suppose you have automated a process to restore the database from production instance to test instance. We might need to get the database history to get answers to the following questions.
- What is the last database restoration date?
- What are the details of the database restoration (Source backup file, backup file creation date)?
- Who performed the database restoration?
You can get the database restoration information using the system tables in the MSDB database. Below are tables in the MSDB database for the database restoration history.
Restore history
This table contains the row for each database restoration performed. In the following image, we can see the essential columns of this table:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [restore_date] ,[destination_database_name] ,[user_name] ,[backup_set_id] ,[restore_type] ,[replace] ,[recovery] ,[restart] FROM [msdb].[dbo].[restorehistory] |
We get the following database restoration history in my environment.
restore_date: It shows the database restoration date.
destination_database_name: We can get the destination database name using this column.
user_name: it gives user name that performed the restoration for that particular database.
backup_set_id: We can join this column with backupset table to get information about the backup file.
restore_type: We can use this column to know the kind of database restoration performed on particular database.
- D – Database
- I -Differential
- L – Log
- V – Verifyonly
replace: once we execute a database restore command, we set this option to replace the existing destination database.
- 1 – Specified
- 0 – Not specified
recovery: In the database restore query, we also specify the Recovery and Norecovery option to bring the database open for users or not.
- 1 – RECOVERY
- 0 – NoRecovery
Restart: It shows whether the restore operation specified the RESTART option or not.
- 1-Specified
- 0-Not specified
restorefile
We get the row for each restored file. We can join this table with restorehistory table on the restore_history_id column as well.
Destination_phys_name: It gives the name of the physical file with the complete path. You will get the detail of each physical file that was restored by the backup file.
restorefilegroup
We can do filegroup restore as well in SQL Server. A FILEGROUP backup and restore allows restoring the objects related to specific filegroup only. It is useful mainly for huge databases in TB’s. Each database has Primary filegroup that contains primary data file MDF.
|
1 2 3 |
SELECT [restore_history_id] ,[filegroup_name] FROM [msdb].[dbo].[restorefilegroup] |
[restore_history_id]: We can join this column with other MSDB tables to get more information.
Filegroup_name: It is the name of the FILEGROUP on which restoration was performed.
Let us fetch information from the MSDB using internal tables with the following query. In this query, we join the restrehistory and restorefile tables with the backup history information tables to get complete information.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT rh.destination_database_name AS [Database], CASE WHEN rh.restore_type = 'D' THEN 'Database' WHEN rh.restore_type = 'F' THEN 'File' WHEN rh.restore_type = 'I' THEN 'Differential' WHEN rh.restore_type = 'L' THEN 'Log' ELSE rh.restore_type END AS [Restore Type], rh.restore_date AS [Restore Date], bmf.physical_device_name AS [Source], rf.destination_phys_name AS [Restore File], rh.user_name AS [Restored By] FROM msdb.dbo.restorehistory rh INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id ORDER BY rh.restore_history_id DESC GO |
We get the information about the database restoration as per the following screenshot.
Conclusion
In this article, we explored getting database restoration history. It is essential for the DBA to be familiar with the way of getting this information in a handy way.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023