Bobcares

WeSupport

Call Us! 1-800-383-5193
Call Us! 1-800-383-5193
Call Us! 1-800-383-5193

Need Help?

Emergency Response Time custom

Our experts have had an average response time of 11.06 minutes in March 2021 to fix urgent issues.

We will keep your servers stable, secure and fast at all times for one fixed price.

SQL server database last restore time – How to find it

by | Jan 10, 2021

We can find the SQL server database last restore time using the system table in msdb.

As part of our Server Management Services, we assist our customers with several SQL queries.

Today, let’s focus on Database Backup and the last restore time.

 

SQL server database last restore time

Database Backup and restores are common tasks for any database administrator. Similarly, we can also get information about the last restore time of the database.

There is a system table named restorehistory in msdb system database that captures this information. We need to gather information from this table to get the data.

We run the T-SQL code below to get this info:

use msdb go select Destination_database_name AS [DB Name],user_name AS [User] ,restore_date As [Last Restore Date] from restorehistory where Destination_database_name like (‘qa%’)

Once we run the above command, we will get output. Here, we can see the database name, a user that has restored the database, and the last restore date and time.

SQL server database last restore time

 

Restore history

Restorehistory system table is very informative in case we need information for audit or database forensics purposes.

This table contains the row for each database restoration performed.

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.
  • restore_date: It shows the database restoration date.
  • destination_database_name: Here, we can get the destination database name using this column.
  • user_name: Gives the 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 a 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.

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 the restore 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. We will get the detail of each physical file.
  • restorefilegroup: We can do filegroup restore as well in SQL Server.

A FILEGROUP backup and restore allows restoring the objects related to a specific filegroup only. Generally, each database has a Primary filegroup that contains the primary data file MDF.

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.

For instance, we join the restrehistory and restorefile tables with the backup history information tables to get complete information.

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

[Need help to find out the last restoration? We’d be happy to assist]

 

Conclusion

To conclude, the SQL server database last restore time can be found using the system table in msdb. Today, we saw how our Support Engineers collect database restoration history.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF