How to query last restore date in SQL Server?

  • marc_s

    marc_s Correct answer

    8 years ago

    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
    

    records in `restorehistory` may be cleaned by `sp_delete_backuphistory`.

  • The script that can be used is:

    declare @DB sysname = 'MyDB';
    select * from msdb.dbo.restorehistory where destination_database_name = @DB;
    
  • 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 that in addition to restore date it returns backup date which was also useful in my case

  • 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 
    

    Msg 208, Level 16, State 1, Line 2 Invalid object name 'restorehistory'.

  • 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
    
  • 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'
    
  • 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/

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM