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
    
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
add comment

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

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

2 Answers

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
add comment

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
add comment

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.