Rajendra Gupta
SQL database restore history

How to get a SQL database restore history

March 11, 2019 by

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:

We get the following database restoration history in my environment.

SQL database restore history

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.

SQL database restore history - a row for each restored file

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.

SQL database restore history - 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.

We get the information about the database restoration as per the following screenshot.

SQL database restore history - information about database restoration

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

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 rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
109 Views