Home > SQL server DBA administration > To know the all the information about the restored databases

To know the all the information about the restored databases


As per our day to job we have to restore the database in different server as per the requirement and some time we have to give all the information to business owner to validate the task done by person . The business owner required the information who , when and what backup is used to restore the database . We can use the below script to get to know all the information whatever we are looking for.

 

Script:–

 

SQL 2005 script:

 

DECLARE @dbname sysname, @days int

SET @dbname = NULL –substitute for whatever database name you want

SET @days = -30 –previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

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’

  WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’

  WHEN rsh.restore_type = ‘R’ THEN ‘Revert’

  ELSE rsh.restore_type

 END AS [Restore Type],

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, ISNULL(@days, -30), GETDATE()) –want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all

ORDER BY rsh.restore_history

 

 

SQL 2008 and 2008R2:–

 

–Script: small change is there

 

DECLARE @dbname sysname, @days int

SET @dbname = NULL –substitute for whatever database name you want

SET @days = -30 –previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

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’

  WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’

  WHEN rsh.restore_type = ‘R’ THEN ‘Revert’

  ELSE rsh.restore_type

 END AS [Restore Type],

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, ISNULL(@days, -30), GETDATE()) –want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all

ORDER BY rsh.restore_history_id

 

 

 

 

Result set:–

 

Database

Restored By

Restore Type

Restore Started

Restored From

AdventureWorks

RAJIB\Rajib

Database

2012-11-24 13:55:10.103

D:\SQL Database Backup\AdventureWorks_backup_2012_10_26_075210_1174434.bak

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: