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