SELECT TOP 100
	BS.backup_finish_date, 
	BMF.physical_device_name
	, *
FROM	msdb..backupmediafamily BMF
	JOIN msdb..backupmediaset BMS
		ON BMF.media_set_id = BMS.media_set_id
	JOIN msdb.dbo.backupset BS
		ON BS.media_set_id = BMS.media_set_id
WHERE	    BS.database_name = 'MyDatabase'	-- SELECT DB_NAME()
-- 	AND BS.backup_finish_date BETWEEN '01-Mar-2005' AND '16-Mar-2005'
ORDER BY BS.backup_finish_date DESC

-- Restore History for DB - RestoreFile Dataset
SELECT DISTINCT TOP 100
--	RH.destination_database_name,
	RH.restore_history_id,
--  	BF.file_number,
	RF.file_number,
	RH.restore_date,

	RF.file_number,
	RF.destination_phys_drive,
	RF.destination_phys_name,

	RG.filegroup_name,

	[Backup Set Name]=BS.name,
	BS.description,
	BS.database_creation_date,
	BS.backup_start_date,
	BS.database_name,
	BS.server_name,

-- 	[Backup File Name] = BF.physical_name,
-- 	BF.logical_name,
-- 	BF.file_size,
-- 
-- 	[Media Set Name] = BMS.name,
-- 	BMF.physical_device_name,

	[--RH--] = '--RH--',
	RH.*,
	[--RF--] = '--RF--',
	RF.*,
	[--RG--] = '--RG--',
	RG.*,
	[--BS--] = '--BS--',
	BS.*
-- 	[--BF--] = '--BF--',
-- 	BF.*,
-- 	[--BMS--] = '--BMS--',
-- 	BMS.*,
-- 	[--BMF--] = '--BMF--',
-- 	BMF.*
FROM	msdb.dbo.restorehistory RH
	LEFT OUTER JOIN msdb.dbo.restorefile RF
		ON RF.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.restorefilegroup RG
		ON RG.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.backupset BS
		ON BS.backup_set_id = RH.backup_set_id
-- 	LEFT OUTER JOIN msdb.dbo.backupfile BF
-- 		ON BF.backup_set_id = RH.backup_set_id
-- 	LEFT OUTER JOIN msdb.dbo.backupmediaset BMS
-- 		ON BMS.media_set_id = BS.media_set_id
-- 	LEFT OUTER JOIN msdb.dbo.backupmediafamily BMF
-- 		ON BMF.media_set_id = BS.media_set_id
WHERE	    RH.destination_database_name = N'MyDatabase'	-- SELECT DB_NAME()
-- 	AND RH.restore_type = 'D' 
ORDER BY RH.restore_date DESC
--  	, BF.file_number DESC
	, RF.file_number DESC