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.
This table contains the row for each database restoration performed. In the following image, we can see the essential columns of this table:
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.
- 0-Not specified
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.
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.
[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.
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'
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
We get the information about the database restoration as per the following screenshot.
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.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at firstname.lastname@example.org
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Overview of DBCC INPUTBUFFER and sys.dm_exec_input_buffer DMF - November 7, 2019
- Exploring the SandDance Visualizations extension in Azure Data Studio - November 6, 2019
- A handy SQL Notebook for the purposes of troubleshooting in Azure Data Studio - November 5, 2019