select 
	/*Restore History*/
	  rh.restore_date [Restore Date]
	, case 
		when rh.restore_type='D' then 'Database'
		when rh.restore_type='F' then 'File'
		when rh.restore_type='G' then 'FileGroup'
		when rh.restore_type='I' then 'Differential'
		when rh.restore_type='L' then 'Log'
		when rh.restore_type='V' then 'VerifyOnly'
		when rh.restore_type='R' then 'Cancel'
		else rh.restore_type end as [Restore Type]
	, rh.destination_database_name [Destination DB]
	, rh.[user_name] [Restored By]
	
	/* backup media family */
	, bmf.physical_device_name [Backup Device]
	, case 
		when bmf.device_type=2 then 'Disk'
		when bmf.device_type=5 then 'Tape'
		when bmf.device_type=7 then 'Virtual Device'
		when bmf.device_type=105 then 'Backup Device'
		else convert(varchar(10),bmf.device_type) end [Device Type] 
		
	/*Backupset Info*/
	, bs.name [Backupset Name]
	, bs.[description] [Backupset Desc]
	, case
		when bs.[type] = 'D' then 'Database' 
		when bs.[type] = 'I' then 'Diff DB' 
		when bs.[type] = 'L' then 'Log' 
		when bs.[type] = 'F' then 'File or FG' 
		when bs.[type] = 'G' then 'Diff File' 
		when bs.[type] = 'P' then 'Partial' 
		when bs.[type] = 'Q' then 'Diff Part'
		else bs.[type] end as [Backup Type]
	, convert(money,bs.backup_size/1024./1024.) [Backup Size (MB)]
	, convert(money,bs.compressed_backup_size/1024./1024.) [Backup Size Compressed (MB)]
	, convert(decimal(5,2),100-bs.compressed_backup_size/bs.backup_size*100) [Compression %]
	, bs.server_name [Source SQL]
	, convert(varchar(10),bs.software_major_version)+'.'
		+ convert(varchar(10),bs.software_minor_version)+'.'
		+ convert(varchar(10),bs.software_build_version) [SQL Version]
	, bs.[database_name] [Source DB (SDB)]
	, bs.database_creation_date [SDB Created]
	, bs.[compatibility_level] [SDB CompLevel]
	, bs.[collation_name] [SDB Collation]
	, bs.recovery_model [SDB Recovery]
	
	, bs.[user_name] [Backup Made By]
	, bs.backup_start_date [Backup Started]
	, bs.backup_finish_date [Backup Finished]

	/* LSNs */
	, bs.database_backup_lsn
	, bs.first_lsn
	, bs.last_lsn
	, bs.checkpoint_lsn
	
	/*Restore Options*/
	, rh.[replace] [With Replace]
	, rh.[recovery] [With Recovery]
	, rh.stop_at
	, rh.stop_at_mark_name
	, rh.stop_before
from msdb.dbo.restorehistory rh 
   join msdb.dbo.backupset bs on rh.backup_set_id=bs.backup_set_id
   join msdb.dbo.backupmediafamily bmf on  bs.media_set_id=bmf.media_set_id
order by rh.restore_history_id desc