96

Is there a T-SQL query that shows the last restore datetime for a certain database?

1

8 Answers 8

155

This will list all "most recent" restores for each database on your server:

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1
1
  • 3
    records in restorehistory may be cleaned by sp_delete_backuphistory. May 12, 2017 at 7:24
18

The script that can be used is:

declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;
16

main table for this purpose is msdb..restorehistory

SELECT  [rs].[destination_database_name] ,
        [rs].[restore_date] ,
        [bs].[backup_start_date] ,
        [bs].[backup_finish_date] ,
        [bs].[database_name] AS [source_database_name] ,
        [bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM    msdb..restorehistory rs
        INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
        INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

from mssqltips

1
  • 3
    +1 that in addition to restore date it returns backup date which was also useful in my case
    – sarh
    Sep 5, 2018 at 13:21
4

It will give you the latest refresh date for your DB:

use [msdb]
select top 1 * from restorehistory 
where destination_database_name='DB_NAME'
order by restore_history_id desc 
1
  • Msg 208, Level 16, State 1, Line 2 Invalid object name 'restorehistory'. Mar 13, 2017 at 15:22
2

Adding important information about OS User name and Machine Name:

Select Destination_database_name, 
       restore_date,
       database_name as Source_database,
       Physical_device_name as Backup_file_used_to_restore,
       bs.user_name,
       bs.machine_name
from msdb.dbo.restorehistory rh 
  inner join msdb.dbo.backupset bs 
    on rh.backup_set_id=bs.backup_set_id
  inner join msdb.dbo.backupmediafamily bmf 
    on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC
2

Is there a T-SQL query that shows the last restore datetime for a certain database?

Hi, I have generated the below script which can give you much more detailed information about your query.

Query to get the "Most Recent Restore" information about the specific database:

WITH MostRecentRestore AS
(
SELECT 
RowNum = ROW_NUMBER() OVER (PARTITION BY RH.Destination_database_name ORDER BY RH.Restore_Date DESC),
RH.Restore_date, 
BS.[database_name] as Source_Database, 
RH.Destination_Database_Name, 
BS.Backup_Start_Date, 
BS.Backup_Finish_Date, 
CASE WHEN RH.restore_type = 'D' THEN 'Database'
  WHEN RH.restore_type = 'F' THEN 'File'
  WHEN RH.restore_type = 'G' THEN 'Filegroup'
  WHEN RH.restore_type = 'I' THEN 'Differential'
  WHEN RH.restore_type = 'L' THEN 'Log'
  WHEN RH.restore_type = 'V' THEN 'Verifyonly'
  WHEN RH.restore_type = 'R' THEN 'Revert'
  ELSE RH.restore_type 
END AS Restore_Type,
RH.[Replace],
RH.[Recovery],
RH.Restore_Date AS Restored_On,
BMF.physical_device_name AS Restored_From,
RF.destination_phys_name AS Current_DB_File_Location,
RH.user_name AS Restored_By,
BS.machine_name,
BS.Server_Name
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 bs.media_set_id = bmf.media_set_id
)
SELECT *
FROM MostRecentRestore
WHERE [RowNum] = 1 AND destination_database_name = 'YourDatabaseName'
1

Necromancing.
A bit old those row_numbers in CTEs.
Here more modern SQL:

-- Records in restorehistory may be cleaned by sp_delete_backuphistory. 
-- USE msdb; GO EXEC sp_delete_backuphistory @oldest_date = '01/14/2010';  

SELECT 
     dbs.name AS db_name 
    ,dbs.create_date 
    ,dbs.compatibility_level 
    ,dbs.collation_name 
    ,dbs.recovery_model_desc 
    ,dbs.snapshot_isolation_state_desc 
    ,dbs.user_access_desc 
    ,dbs.state_desc 
     
    ,dbs.physical_database_name 
    ,dbs.log_reuse_wait_desc 
     
    ,tRestoreHistory.restore_date 
FROM master.sys.databases AS dbs 

OUTER APPLY 
    (
        SELECT restore_date 
        FROM msdb.dbo.restorehistory AS rhistory 
        WHERE rhistory.destination_database_name = dbs.Name 
        ORDER BY rhistory.restore_date DESC 
        OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY 
    ) AS tRestoreHistory 

WHERE (1=1) 
-- AND restore_date < CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime) 

ORDER BY restore_date DESC, create_date DESC 
0
Select Destination_database_name, 
       restore_date,database_name as Source_database,
       Physical_device_name as Backup_file_used_to_restore 
from msdb.dbo.restorehistory rh 
  inner join msdb.dbo.backupset bs 
    on rh.backup_set_id=bs.backup_set_id
  inner join msdb.dbo.backupmediafamily bmf 
    on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC

For detailed information you can check below link:

http://www.passionforsql.com/how-to-find-when-last-database-backuprestore-was-done/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.