SQL Script: - How to find the database restoring history of SQL Server databases

, 2018-03-30

Sometime we need to find out the history of database restoration to get the answers of following questions: -

1) Do we need to restore the database again as there can be a possibility that it is restored recently?
2) Which backup file (with location information) is used for database restoration?
3) Name of the source database whose backup file is used database restoration?
4) When database backup file is created ?
5) Which user perform the database restoration activity?

Below query can be used for finding the database restoration history

SELECT [r].[destination_database_name] as [Database Name], 
[r].[restore_date] [Restore Date], 
[r].user_name as [User],
(cast(.software_major_version as varchar(10))+'.' + 
cast(.software_minor_version as varchar(10)) +'.'+
cast(.software_build_version as varchar(10))) as [Server version],
.compressed_backup_size as [Backup file Size],
.[backup_start_date] [Backup Start Time], 
.[backup_finish_date][Backup End Time], 
.[database_name] as [Source Database Name], 
[bkpmf].[physical_device_name] as [Backup File Used For Database Restore]
FROM msdb..restorehistory r
INNER JOIN msdb..backupset b
ON [r].[backup_set_id] = .[backup_set_id]
INNER JOIN msdb..backupmediafamily bkpmf 
ON .[media_set_id] = [bkpmf].[media_set_id] 
ORDER BY [r].[restore_date] DESC

For example, suppose we created a database name as "Sampledatabase" which is never restored. Then if we execute the above query we got no restoration history of this database:-

Now we take backup of this database 

and restore the "Sampledatabase" with the backup taken in the above step.

If we again execute the SQL Script to get database restoration history, we got the following result

Now if we take another backup with the different name as "SampleDatabase_1.bak" on the same location and tried to restore the database again, then one more entry for the Sampledatbase will come on the execution of the SQL query for getting Database restoration history as shown in below images

If we see the outcome of the query, we also got the version of the SQL server on which database backup file is created & the size of the backup file. Sometime these additional information can become handy.

I hope database guy will get help from this query. Please send me your valuable feedback on askvivekjohari@gmail.com. Thanks for visiting my blog.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads