How to Check the Recent Restore Details

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:

-- Restoring databases

-- Set the corresponding database offline to disconnect all users and then restore

-- ********************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ********************************************************************************

USE MASTER

GO

ALTER DATABASE [Venti]

SET OFFLINE

WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Venti] FROM DISK = 'E:\SQLBackup\Venti_20160324212850.bak' WITH REPLACE

USE MASTER

GO

ALTER DATABASE [Grande]

SET OFFLINE

WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Grande] FROM DISK = 'E:\SQLBackup\Grande_20160324212851.bak' WITH REPLACE

USE MASTER

GO

ALTER DATABASE [Tall]

SET OFFLINE

WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Tall] FROM DISK = 'E:\SQLBackup\Tall_20160324212851.bak' WITH REPLACE

Script: RestoreDatabases.sql

Output:

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

-- Checking the recent restore details

-- ********************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ********************************************************************************

SELECT

rsh.destination_database_name AS [Database]

, bs.recovery_model

, rsh.user_name AS [Restored By]

, CASE

WHEN rsh.restore_type = 'D' THEN 'Database'

WHEN rsh.restore_type = 'F' THEN 'File'

WHEN rsh.restore_type = 'G' THEN 'Filegroup'

WHEN rsh.restore_type = 'I' THEN 'Differential'

WHEN rsh.restore_type = 'L' THEN 'Log'

ELSE rsh.restore_type END AS [Restore Type]

, CASE

WHEN rsh.replace = 1 THEN 'WITH REPLACE'

WHEN rsh.replace = 0 THEN 'NOT SPECIFIED'

END AS [Restore Option]

, CASE

WHEN rsh.recovery = 1 THEN 'WITH RECOVERY'

WHEN rsh.recovery = 0 THEN 'WITH NORECOVERY'

WHEN rsh.recovery IS NULL THEN 'WITH RECOVERY'

END AS [Recovery Option]

, rsh.restore_date AS [Restore Started]

, bmf.physical_device_name AS [Restored From]

, rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, -1, GETDATE()) -- Search the past day restore history

AND rsh.destination_database_name IN('Venti','Grande','Tall') -- Specify the databases you wish to see the restore histories or comment this line to see all

Script: RestoreHistoryDetails.sql

Output:

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.