Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

DBA SQL Database Restoration

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

Byvivekjohari

Mar 30, 2018 #DBA Scripts, #SQL Script for finding database restoration history

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

Trending
Exception handling in SQL Server

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.

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

DBA

SQL Script to find the database & their files details on a SQL Server

Mar 30, 2018 vivekjohari
DBA

SQL Script: – How to find the last access date of a database

Mar 29, 2018 vivekjohari
DBA SQL Tips

Learn How to Recover Deleted Records from SQL Server 2014

Aug 8, 2017 Andrew Jackson
29 thought on “SQL Script: – How to find the database restoring history of SQL Server databases”

  17. … [Trackback]

    […] Here you can find 6502 additional Info on that Topic: techmixing.com/2018/03/sql-script-how-to-find-database.html […]

    Reply

  29. It’s like you’re reading my thoughts! You seem so knowledgeable about this topic, almost like you’ve written the book on it. A few more visuals might enhance it even more. I’ll certainly be back!

    Reply

Leave a Reply

You missed

Career Coaching

How to Update Your Resume After a Career Break: A Strategic Approach

February 28, 2025 Megha No Comments
Career Coaching

The Importance of Soft Skills in the Modern Workplace

February 28, 2025 Megha No Comments
Career Coaching

Unlocking the Power of Resume Keywords: Your Guide to Standing Out

February 28, 2025 Megha No Comments
Career Coaching

Top 10 High-Demand Careers in India for 2025: Secure Your Future Today

February 28, 2025 Megha No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading