How to Check the Recent Restore Details

By | In SQL Server | July 19th, 2016

As part of the migration project steps, you need to create a report to show the restore details such as the restore option, recovery options, restore start time, source file, and where the file was restored. In this blog, we will explore how we can accomplish that by first running the restores for three tiny databases and then run the script to check the restore details.

Here are the explanations of the system tables, functions, conversions, and case expressions that you need to know to get started:

  • MSDB.DBO.RESTOREHISTORY: Contains one row per restore operation.
  • MSDB.DBO.RESTOREFILE: Contains one row per file restored.
  • MSDB.DBO.BACKUPSET: This system table stores the information about the backup operations. It contains critical information such as the backup start date, backup end date, backup size, backup type, and other important backup details.
  • MSDB.DBO.BACKUPMEDIAFAMILY: Contains one row for each media family. One of the most important data column in this system table is the physical_device_name data column. It identifies the physical path of the backup.
  • GETDATE(): Returns the current date and time on which the instance of SQL Server is running.
  • DATEADD(): Returns a specified date with the specified number interval added to a specified datepart of that date. Syntax: DATEADD (datepart , number , date )
  • CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this blog, we will use the searched case expression.

Prior to restoring the databases, you need to disconnect all connections. You can set the database offline to ensure no one will be using it and then run the restore command. The following is example of restoring three tiny databases:

Script: RestoreDatabases.sql

Output:

Restore DB

After the restores are completed, we can now query the restore details to validate the correct files are restored:

Script: RestoreHistoryDetails.sql

Output:

Restore History Details

After you had restored the databases, the next step will usually require you to resolve any orphaned users of which we will be exploring in a future blog.

Datavail Script: Terms & Conditions

By using this Oracle upgrade software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).