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

By:   |   Comments (27)   |   Related: > Restore


Problem

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.

Solution

The restore information is readily available inside the msdb database, making the solution as easy as a few lines of T-SQL.

When I ask people about how they verify their database restores I often get back a response that includes something similar to the following code:

RESTORE VERIFYONLY FROM DISK = 'G:\dbname.bak'

The above command simply returns this message when successful: "The backup set on file 1 is valid." Is that really useful for your end user that is complaining that the data is not correct? Chances are their complaint is not about if the backup set was valid, but more specifically it is about your selection of the backup file, or the timing of the backup itself.

If the backup was done at the wrong time, or if you restored from the wrong backup file, then the end user may be seeing exactly that problem while reviewing the data. So, how do you provide some proof that you did the restore from the correct backup file? The following script can give you this information.

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

The script will return the following result set:

Column Name Description
destination_database_name The name of the database that has been restored.
restore_date The time at which the restore command was started.
backup_start_date The time at which the backup command was started.
backup_finish_date The time at which the backup command completed.
source_database_name The name of the database after it was restored.
backup_file_used_for_restore The file(s) that the restore used in the RESTORE command.

Here is a screenshot of a sample result set returned by the script.

adventure works
Next Steps
  • Take the above code and execute against your instance.
  • Keep this script handy next time you want to know when a database was restored, what file it came from and when the backup actually occurred
  • Take a look at these other Backup and Recovery tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, March 22, 2023 - 1:06:44 PM - JC Back To Top (91034)
Great script -- thanks!

Tuesday, April 20, 2021 - 5:07:24 AM - Mathew Back To Top (88570)
Hi I am newbie, can you please tell me how to add master db into this script? So I can see when was the master db restored in single mode last time? Thank you in advance

Monday, May 18, 2020 - 9:13:22 AM - dan Back To Top (85687)

thanks for script ... but is there a way to find duration of the restore at all?


Wednesday, January 29, 2020 - 5:32:42 PM - Greg Robidoux Back To Top (84040)

Hi Cathy, 

You can just copy the script as is and run it.  This will show any restores that have been done as long as the data still exists in the system tables that are used in the query to gather the information.

-Greg


Wednesday, January 29, 2020 - 4:52:31 PM - Cathy Back To Top (84039)

Thomas,

Newbie here.  I copied the code into an SQL window and the script did not return any data. Do I need to modify anything for my table names or is this code generic so that it doesn't matter?

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

Thanks,

Cathy


Monday, February 4, 2019 - 10:55:54 PM - Timothy Noel Back To Top (78965)

Thomas, 

Excellent script!  

In our shop, databases are refreshed from one server to another all the time. I needed to know the last time a specific database was refreshed, and from where.  This script brought up the exact data I was looking for.  Thank you!


Monday, November 26, 2018 - 9:21:50 AM - Douglas Osborne Back To Top (78327)

Thomas,

Can you tell if the backup was compressed? I see 'Compressed_Backup_Size' - but does that signify it was compressed?

 

Thanks,

Doug 


Tuesday, March 6, 2018 - 1:34:30 AM - Joel Greijer Back To Top (75354)

The article states that [bmf].[physical_device_name] as [backup_file_used_for_restore]

This is wrong. The backupmediafamily.physical_device_name contains the original backup-to-path from the server who was backed up.

It could therefore be named [filepath_set_by_orginal_backup_process_this_is_not_from_where_you_restored]

Joel

 

 


Wednesday, December 7, 2016 - 6:14:50 AM - SP Sarkar Back To Top (44912)

 

 Excellent code. Thank you 


Sunday, October 30, 2016 - 3:31:02 AM - Subha G Back To Top (43655)

We do use a lot of DMV's but the point is when to use each one of them aptly.. this post is perfect under the naming " How to find the last_restored_file on secondary server in Log Shipping when Log Shipping monitor shows NULL values"..
I did googled a lot but when specified how to sync up the values with probable causes , but this made me sure that we have an alternate way to find..

 

Gooood job Thomas..!!!!!


Wednesday, September 21, 2016 - 11:57:49 AM - vanrani Back To Top (43377)

 

Thank you for the Tip, it is really helpful. I have been using this long time, just today getting confused. I am restoring a db form the backup at the network share and using the UNC path. The restore is successful. But when used the query from this article, it is not showing the network share path instead keep showing the path where the old backups are in the local disk.

 

So i removed the old backup from the local disk and run the restore job (SQLCMD) and after restore still i am seeing the backup from the local disk. But i am sure it is picking up the backup file from the network share, i confirmed this by removing the backup file from the network share to see if the job fails.

 

So my question to gurus out there, why the query in this aritcle is not giving me right info when i used the network share for backup file.

 

Below is my cmd in the agent job.

 

sqlcmd  -S SQLServ1\ABC -E -Q "ALTER DATABASE UserDB1  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;restore database UserDB1 from disk = '\\SQL1001BKP\MaskedDB_Backup\BKCopy\ProdCopy.bak' with FILE = 1,MOVE N'CCSCON_Data' TO N'F:\SQLServ1\SQLServ1_DATA\MSSQL11.CLMS\MSSQL\DATA\ProdCopy.mdf', MOVE N'CCSCON_Log' TO N'F:\SQLServ1\SQLServ1_TLOG_SSD\MSSQL11.CLMS\MSSQL\DATA\ProdCopy_log.ldf',REPLACE;ALTER DATABASE UserDB1  SET MULTI_USER;"

 

Any feedback will be great!

Thank you!!

 


Sunday, December 27, 2015 - 1:36:04 AM - AJIT Back To Top (40311)

 

 

I WISH TO KNOW HOW TO CHECK WHEN DATABASE WAS ATTATCHED


Tuesday, June 23, 2015 - 5:06:06 PM - Greg Robidoux Back To Top (38003)

Hi Mike,

I took a look at the MSDN link and it shows all columns can be NULL.  It doesn't say when this will occur, so I guess based on the MSDN post it is possible, but I would think this is still a pretty reliable method.

-Greg


Tuesday, June 23, 2015 - 2:45:55 PM - Mike Back To Top (37999)

 

!!!ERROR in Article!!!

Dear Author,

 

Per MSDN article https://msdn.microsoft.com/en-us/library/ms187408.aspx

restore_date


<-- Date and time of the completion of the restore operation. Can be NULL.

There is an obvious error in this article.

 

Thanks.


Friday, March 27, 2015 - 12:34:26 PM - Mario Back To Top (36731)

Thanks a lot for this, is very helpful. I'll keep it to re-use it as i have these kind of requests  from customers very often. It means from now on i will save a lot of time thanks to you, hope i can pay back someday.

Cheers,


Monday, July 7, 2014 - 9:00:35 AM - SQLALI Back To Top (32573)

I have Restored the Database and we can see that it showing old created date. I have followed below procedure. It will be very helpful if someone provides me the reason.

1) Restored by using restore script.
Note: There is no issue with script. Restored many DB'S by using this.
2) Verified by using sp_helpdb [db_name]
I see the old date i.e of 2012 and Owner is SA. Generally it should be restored date and my self should be owner.
3) By error log i can see that database is restored successfully.

Thanks,
Ali.


 


Tuesday, June 18, 2013 - 7:52:57 AM - Valerie Gurshman Back To Top (25463)

Thank you very much for post, exactly what I am looking for!


Monday, April 29, 2013 - 9:35:56 AM - Rolando Perez Back To Top (23610)

Excellent, Thanks


Friday, March 29, 2013 - 4:54:41 AM - Jack Reyn Back To Top (23067)

Thanks - really useful.


Wednesday, March 13, 2013 - 7:24:37 AM - Fawad Back To Top (22776)

Thanks for brilliant script. 

 

Regards.


Wednesday, October 28, 2009 - 12:39:02 PM - admin Back To Top (4326)

We received an email on the value of this tip and wanted to pass along the comments...

Here is a comment from Jerry L. on this tip:

set nocount off
go
drop procedure getDBRestoreDate
go
create procedure getDBRestoreDate
  @DatabaseName nvarchar(255)
 ,@DatabaseRestoreType varchar(4) -- Use 'Full','Inc','Log' to set what kind of restores to look at when getting the restore date
as
begin
    --drop table #restore_list

    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],
    case bs.type when 'D' then 'Full' when 'I' then 'Inc' when 'L' then 'Log' else 'Unk' end as [backup_type]
    into #restore_list
    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]
    INNER JOIN sys.databases d on rs.[destination_database_name]=d.name and d.name = @DatabaseName -- if the database doesn't exist, return null
    ORDER BY [rs].[restore_date] DESC

    --drop table #restore_active_list

    select max([restore_date]) as restore_date, [destination_database_name], [backup_type]
    into #restore_active_list
    from #restore_list
    group by [destination_database_name], [backup_type]

    --select * from #restore_active_list order by [destination_database_name],restore_date

    delete rl1
    --select *
    from #restore_active_list rl1, #restore_active_list rl2
    where rl2.backup_type='Full'
    and rl1.restore_date < rl2.restore_date
    and rl1.[destination_database_name]=rl2.[destination_database_name]
    -- any restore prior to the most recent full does not count

    delete rl1
    --select *
    from #restore_active_list rl1, #restore_active_list rl2
    where rl2.backup_type='Inc' and rl1.backup_type='Log'
    and rl1.restore_date < rl2.restore_date
    and rl1.[destination_database_name]=rl2.[destination_database_name]
    -- any 'Log' restore prior to the most recent 'Inc' doesn't count
   
    select 'Full' as ToRestore,'Full' as FromRestore, 0 LogLogMatch
    into #To_From_Trade
    union all
    select 'Inc','Full',0
    union all
    select 'Inc','Inc',0
    union all
    select 'Log','Full',0
    union all
    select 'Log','Inc',0
    union all
    select 'Log','Log',1
   
    select max(rl.backup_start_date) as max_backup_start_date
    ,max(T.LogLogMatch) as LogLogMatch
    --*
    from #restore_active_list arl, #restore_list rl, #To_From_Trade T
    where arl.restore_date=rl.restore_date
    and arl.[destination_database_name]=rl.[destination_database_name]
    and arl.[backup_type]=rl.[backup_type]
    and T.ToRestore=@DatabaseRestoreType and T.FromRestore=arl.backup_type
    --order by 1 desc

end
go

Now I can do
exec getDBRestoreDate '<Database Name>', 'FULL'
to get the date of the most recent full for my scripts that restores fulls.

Also, my log-shipping scripts use
exec getDBRestoreDate '<Database Name>', 'LOG'
to get the date of the most recent restore, and then they compare that to the timestamps on the files to find which one if any to restore next.

also, here is a modified version I use for reporting.

go
set nocount off
go
drop procedure getDBRestoreDates
go
create procedure getDBRestoreDates
as
begin
    --drop table #restore_list

    --get all the restores with right data attached
    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],
    case bs.type when 'D' then 'Full' when 'I' then 'Inc' when 'L' then 'Log' else 'Unk' end as [backup_type]
    into #restore_list
    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]
    INNER JOIN sys.databases d on rs.[destination_database_name]=d.name
    ORDER BY [rs].[restore_date] DESC
   
    --select * from #restore_list order by 1,2

    --drop table #restore_active_list

    --get a list of the last of each type for each db
    select max([restore_date]) as restore_date, [destination_database_name], [backup_type]
    into #restore_active_list
    from #restore_list
    group by [destination_database_name], [backup_type]

    --select * from #restore_active_list order by [destination_database_name],restore_date
   
    --remove the old restores
    delete rl
    from #restore_list rl
    inner join #restore_active_list ral
    on rl.[destination_database_name]=ral.[destination_database_name]
    and rl.[backup_type]=ral.[backup_type]
    and rl.restore_date<ral.restore_date

    -- any restore prior to the most recent full does not count
    delete rl1
    --select *
    from #restore_active_list rl1, #restore_active_list rl2
    where rl2.backup_type='Full'
    and rl1.restore_date < rl2.restore_date
    and rl1.[destination_database_name]=rl2.[destination_database_name]

    -- any 'Log' restore prior to the most recent 'Inc' doesn't count
    delete rl1
    --select *
    from #restore_active_list rl1, #restore_active_list rl2
    where rl2.backup_type='Inc' and rl1.backup_type='Log'
    and rl1.restore_date < rl2.restore_date
    and rl1.[destination_database_name]=rl2.[destination_database_name]

    --If you are looking for the most recent time of a certain type of restore, certain other types of restore can override
    select 'Full' as ToRestore,'Full' as FromRestore, 0 LogLogMatch
    into #To_From_Trade
    union all
    select 'Inc','Full',0
    union all
    select 'Inc','Inc',0
    union all
    select 'Log','Full',0
    union all
    select 'Log','Inc',0
    union all
    select 'Log','Log',1
   
    select
      arl.[destination_database_name]
    --, T.ToRestore
    , arl.[backup_type]
    , max(rl.restore_date) restore_date
    , max(rl.backup_start_date) backup_start_date
    , max(rl.backup_finish_date) backup_finish_date
    , min(rl.backup_file_used_for_restore) as ex_backup_file_used_for_restore
    from #restore_active_list arl
    , #restore_list rl
    --, #To_From_Trade T
    where arl.restore_date=rl.restore_date
    and arl.[destination_database_name]=rl.[destination_database_name]
    and arl.[backup_type]=rl.[backup_type]
    --and T.FromRestore=arl.backup_type
    group by arl.[destination_database_name]
    --, T.ToRestore
    , arl.[backup_type]
    order by 1,2,3

end
go
exec getDBRestoreDates

 

 

I have a reporting server, and advanced users of the server often want to know what time the data is from.  This generates data for the report I'm using now.

 


Tuesday, October 27, 2009 - 6:34:56 PM - julienchappel Back To Top (4316)

I used the script which was provided in the tip and it worked well with the databases restored with SQL Server Restore and with Redgate Backup/Restore. Recently as a company policy I started to use Microsoft DPM for backup. The procedure we use to restore a database is as follows:

- recover from the last full express backup latest entry point,

- detach the current database files and re-nema them

- copy the recovered .mdf and .ldf files to the database files' folders

- attach the new files to the database.

Is there any process similar to this tip which I can use to identify which database backup was restored?

Julien


Tuesday, October 20, 2009 - 12:12:26 AM - SankarReddy Back To Top (4253)

Let me chime in on this. There is another way to find this information using the DEFAULT TRACE. Audit Backup/Restore Event is tracked in the DEFAULT TRACE and one can pull in this information. But a caution note that this information may be over written eventually in the default trace. That should be ok I think as this tip focus is mostly for non-production databases. I am writing an article that list outs various benefits of DEFAULT TRACE and hopefully this should be out in November.

 And Tom, thanks for posting this tip. Its helpful.


Saturday, October 17, 2009 - 2:39:33 PM - admin Back To Top (4215)

sql_noob,

OK - Thank you for the additional option.

Does anyone from the community else have any other techniques?

Thank you,
The MSSQLTips Team


Friday, October 16, 2009 - 3:57:08 PM - sql_noob Back To Top (4213)

 my favorite way was to right click on the database and go to properties. it will have the create time which is when it was mounted. and the last backup date. if there is a big difference between the two then something shady is going on


Friday, October 16, 2009 - 7:48:33 AM - admin Back To Top (4206)

To the MSSQLTips Community,

Thomas is running for a board postion @ SQL PASS. 

Please check out Thomas and the remainder of the candidates.  If you are a voting member of SQL PASS, please take the time to learn about the candidates and select the people you believe will serve SQL PASS the best.

http://www.sqlpass.org/AboutPASS/Elections2009/2009SlateofCandidates.aspx

Thank you,
The MSSQLTips Team


Friday, October 16, 2009 - 7:45:15 AM - admin Back To Top (4205)

Thomas,

I can see how this could be beneficial for Developers or QA Engineers to find out the last time the database (Dev or QA) has been restored.

I can also see value in this logic for reporting environments that are refreshed daily.

When do you normally use this logic?

Thank you,
The MSSQLTips Team















get free sql tips
agree to terms