Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (19)   |   Related Tips: More > Restore

Attend these FREE SQL Server 2017 webcasts >> click to register


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.

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


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, December 07, 2016 - 6:14:50 AM - SP Sarkar Back To Top

 

 Excellent code. Thank you 


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

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

 

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

 

 

I WISH TO KNOW HOW TO CHECK WHEN DATABASE WAS ATTATCHED


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

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

 

!!!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

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 07, 2014 - 9:00:35 AM - SQLALI Back To Top

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

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

Excellent, Thanks


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

Thanks - really useful.


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

Thanks for brilliant script. 

 

Regards.


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

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

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

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

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

 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

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

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


Learn more about SQL Server tools