Technical Article

How to get last restore date and orginal DBName.

,

This script will output the Orignal DB Name, Destination DB Name and Last Restore date for the databases. You get a specific database just add a where clause against the appropriate field. It makes use of the restorehistory and backupset tables in the MSDB database.

SELECT
bus.database_name Org_DBName,
Restored_To_DBName,
Last_Date_Restored
FROM
msdb..backupset bus
INNER JOIN
(
SELECT
backup_set_id,
Restored_To_DBName,
Last_Date_Restored
FROM
msdb..restorehistory
INNER JOIN
(
SELECT 
rh.destination_database_name Restored_To_DBName,
Max(rh.restore_date) Last_Date_Restored
FROM 
msdb..restorehistory rh
GROUP BY
rh.destination_database_name
) AS InnerRest
ON
destination_database_name = Restored_To_DBName AND
restore_date = Last_Date_Restored
) As RestData
ON
bus.backup_set_id = RestData.backup_set_id

Rate

4.83 (6)

You rated this post out of 5. Change rating

Share

Share

Rate

4.83 (6)

You rated this post out of 5. Change rating