Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a TSQL query that shows the last restore datetime for a certain database?

share|improve this question

migrated from stackoverflow.com Jan 29 '13 at 1:45

This question came from our site for professional and enthusiast programmers.

    
2nd hit: mssqltips.com/sqlservertip/1724/… –  Mitch Wheat Jan 28 '13 at 9:44
    
@MitchWheat thanks! –  Kees C. Bakker Jan 28 '13 at 9:49

4 Answers 4

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
share|improve this answer

The script that can be used is:

declare @DB sysname = 'MyDB';
select * from msdb.dbo.restorehistory where destination_database_name = @DB;
share|improve this answer

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

share|improve this answer
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/2014/05/how-to-find-when-last-database.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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