/*****************************************************/
/*** Database Restore History Report *****************/
/*** By Default it captures all DB Restore Info ******/
/*** Give a DB name for the parameter @DBName ********/
/*****************************************************/
-- Give a database name to get restore info for a specific DB
DECLARE @DBName VARCHAR(200);
SET @DBName = NULL
SELECT
	rh.destination_database_name		AS 'Destination_DB_Name',
	rh.restore_date				AS 'Restore_Date',
	rh.user_name				AS 'DB_Restored_By',
	CASE rh.restore_type 
	WHEN 'D' THEN 'Full DB Restore'
	WHEN 'F' THEN 'File Restore'
	WHEN 'G' THEN 'Filegroup Restore'
	WHEN 'I' THEN 'Differential Restore'
	WHEN 'L' THEN 'Log Restore'
	WHEN 'V' THEN 'Verify Only'
	END					AS 'Type_Of_Restore',
	bs.server_name				AS 'Baclup_Taken_From',
	bs.user_name				AS 'Backup_Taken_By',
	bs.database_name			AS 'Source_DB_Name',
	bs.backup_start_date			AS 'Backup_Date',
	bmf.physical_device_name		AS 'File_Path'	
FROM	msdb.dbo.restorehistory rh
	INNER JOIN	msdb.dbo.backupset bs	ON	rh.backup_set_id = bs.backup_set_id
	INNER JOIN	msdb.dbo.backupmediafamily bmf	ON	bs.media_set_id = bmf.media_set_id 
WHERE	rh.destination_database_name = ISNULL(@DBName,rh.destination_database_name)
ORDER BY	Restore_Date	DESC;