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

SQL Script to find database restoring history

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 is 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([b].software_major_version as varchar(10))+’.’ +
cast([b].software_minor_version as varchar(10)) +’.’+
cast([b].software_build_version as varchar(10))) as [Server version],
[b].compressed_backup_size as [Backup file Size],
[b].[backup_start_date] [Backup Start Time],
[b].[backup_finish_date][Backup End Time],
[b].[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] = [b].[backup_set_id]
INNER JOIN msdb..backupmediafamily bkpmf
ON [b].[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 was 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.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in DBA, SQL Database Restoration and tagged , . Bookmark the permalink.

28 Responses to SQL Script: – How to find the database restoring history of SQL Server databases

  1. Pingback: seo plans and pricing

  2. Pingback: ket qua bongda truc tuyen

  3. Pingback: diario Abc Argentina

  4. Pingback: cbd

  5. Pingback: social impact marketing

  6. Pingback: The Business Of Getting Business

  7. Pingback: cornhole board decals

  8. Pingback: should cbd have a taste?

  9. Pingback: Empire Market

  10. Pingback: Global Sanitary Ware Market 2019: Anglo American Platinum, Impala Platinum Holdings, SC MMC Norilsk Nickel, Lonmin, Stillwater Mining

  11. Pingback: Southwood Tallahassee FL

  12. Pingback: sahabat qq

  13. Pingback: Sea Games 2019

  14. Pingback: cdr qq

  15. Pingback: pokermas99

  16. Pingback: www.1qiuqiu99.club

  17. Pingback: jbovn

  18. Pingback: www.1lapakqq.site

  19. Pingback: login sbobet888

  20. Pingback: www.1mainqq.site

  21. Pingback: Tallahassee zipcode

  22. Pingback: www.2015louisvuittonoutlet.net

  23. Pingback: cannabidiol

  24. Pingback: paito sgp

  25. Pingback: Lauren

  26. Pingback: newly built houses for sale

  27. Pingback: partenariat instagram

  28. Pingback: navigate here

Leave a Reply