SQLArena

Getting the restore information from the MSDB database is one of the most important concepts that a DBA should be aware of. The concept can come in handy when you must check for restore history and tracking purposes.

The restore information can be queried by connecting to the MSDB database. It can be useful to check some most important data like the last restore date, database name, username, and backup type.

Restorehistory

SELECT
[restore_date]
,[destination_database_name]
,[user_name]
,[backup_set_id]
,[restore_type]
,[replace]
,[recovery]
,[restart]
FROM [msdb].[dbo].[restorehistory]

The information we gather from the restorehistory table are:

Restore_date: Specifies the restore date of the database.

Destination_database_name: Specifies the destination database name.

User_name: Specifies the name of the user who performed the respective database restore.

Backup_set_id: Specifies the ID that can be joined with the backupset table to get more information about the backup file.

Restore_type: Specifies the type of restore that the respective database was carried out. D (Database), I (Differential), L (Log), and V (Verifyonly).

Replace: Specifies the option to replace the destination database. 1 (was specified with replace option), 0 (was specified without replace option).

Recovery: Specifies if the restore was done with a recovery or norecovery option for the database.

Restart: Specifies whether the RESTART option was applied or not.

Restorehistory and Restorefile

The information we gather from the restorefile table is:

Destination_phys_name: Specifies the complete physical file path that was used during the restore.

select a.[restore_date]
,a.[destination_database_name]
,a.[user_name]
,a.[backup_set_id]
,a.[restore_type]
,a.[replace]
,a.[recovery]
,a.[restart]
,b.destination_phys_name
from [restorehistory] a
inner join [restorefile] b
on
a.restore_history_id=b.restore_history_id

Restorehistory, Restorefile, and Restorefilegroup

A restore of filegroup as well can be performed during a restore in SQL Server. If you wish to restore a specific filegroup by avoiding the rest, then we can do so by using the filegroup option.

The information we gather from the restorefilegroup table is:

Filegroup_name: Specifies the name of the filegroup with which the restoration was carried out.

select a.[restore_date]
,a.[destination_database_name]
,a.[user_name]
,a.[backup_set_id]
,a.[restore_type]
,a.[replace]
,a.[recovery]
,a.[restart]
,b.destination_phys_name from [restorehistory] a
inner join [restorefile] b
on
a.restore_history_id=b.restore_history_id
inner join [restorefilegroup] c
on
a.restore_history_id=c.restore_history_id