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


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.


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


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
Backup and restore

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta