Using SQL Server, how can I obtain the restore date for a particular database?

  • 1
    What database platform & version?
    – AMtwo
    May 4, 2017 at 15:23
  • This is a good question, but I believe all answers below (as of 2019/02/19) only tell when the restore starts but NOT when the restore finishes.
    – jyao
    Feb 19, 2019 at 21:58

3 Answers 3


It's a bit difficult to determine exactly what you want from the way your question is worded, but it sounds like this post may give you the needed information

Identify when a SQL Server database was restored, the source and backup date

From the post:

After restoring a database your users will typically run some queries to verify the data is as expected. However, there are times when your users may question whether the restore was done using the correct backup file. In this tip I will show you how you can identify the file(s) that was used for the restore, when the backup actually occured and when the database was restored.

Here's the script from that post:

SELECT [rs].[destination_database_name]
    ,[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

You can find this information easily using dbatools' Get-DbaRestoreHistory. Works for one or all databases and supports SQL Server 2005-vNext.

Get-DbaRestoreHistory -SqlInstance sql2005 -Database db_2005_CL80 | Select -Last 1

Here's some sample output

enter image description here


According to the link below:

WITH LastRestores AS
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    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
FROM [LastRestores]
WHERE [RowNum] = 1

How to query last restore date in SQL Server?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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