top of page

When my SQL Server database was restored ?

Updated: Dec 24, 2021


Do you want to know the last restore date for the SQL Server database? You can query system tables in the MSDB database to find the details.


For example, in the following query, we want to identify the database restoration date for [AdventureWorks2019_Demo] database.



SELECT [d].[name]        AS [Database],
       [d].[create_date] AS [Database Creation Date],
       [d].[compatibility_level],
       rh.restore_date   AS [Database Restoration Date],
       CASE rh.restore_type
         WHEN 'D' THEN 'Full backup'
         WHEN 'I' THEN 'Differential backup'
         WHEN 'L' THEN 'T-log backup'
         WHEN 'F' THEN 'File'
         ELSE NULL
       END               AS [RestoreType]
FROM   master.sys.databases d
       LEFT OUTER JOIN msdb.dbo.[restorehistory] rh
                    ON rh.[destination_database_name] = d.NAME
WHERE  [d].[name] = 'AdventureWorks2019_Demo'
ORDER  BY [database], restore_history_id

As shown below, database was restored from the full backup on the 2021-12-09 23:33:59.907

SQL Server database backup restoration date

Recent Posts

See All

Comments


bottom of page