select 
	row_number () over (order by rh.restore_history_id desc) [N]
	/*Restore History*/
	, convert(varchar(20),rh.restore_date,106)+' '+convert(varchar(20),rh.restore_date,108) [RestoreDate]
	, 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 [RestoreType]
	, rh.destination_database_name [DestinationDB]
	, rh.[user_name] [RestoredBy]

	, bs.server_name [SourceSQL]
	, convert(varchar(10),bs.software_major_version)+'.'
		+ convert(varchar(10),bs.software_minor_version)+'.'
		+ convert(varchar(10),bs.software_build_version) [SQLVersion]

	, bs.[database_name] [SourceDB(SDB)]
	, bs.recovery_model [SDBRecovery]
	, bs.[user_name] [BackupMadeBy]
	, convert(varchar(20),bs.backup_start_date,106)+' '+convert(varchar(20),bs.backup_start_date,108)  [BackupStarted]
	, convert(varchar(20),bs.backup_finish_date,106)+' '+convert(varchar(20),bs.backup_finish_date,108)  [BackupFinished]


	/* backup media family */
	, 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 [DeviceType] 
	, bmf.physical_device_name [BackupDevice]


		
	/*Backupset Info*/
	, bs.name [BackupsetName]
	, bs.[description] [BackupsetDesc]
	, 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 [BackupType]
	, convert(money,bs.backup_size/1024./1024.) [BackupSizeMB]
	, convert(money,bs.compressed_backup_size/1024./1024.) [BackupSizeCompressedMB]
	, convert(decimal(5,2),100-bs.compressed_backup_size/bs.backup_size*100) [Compression]
	
	
	, bs.database_creation_date [SDBCreated]
	, bs.[compatibility_level] [SDBCompLevel]
	, bs.[collation_name] [SDBCollation]

	

	/* LSNs 
	, bs.database_backup_lsn
	, bs.first_lsn
	, bs.last_lsn
	, bs.checkpoint_lsn
	*/
	/*Restore Options*/
	, rh.[replace] [WithReplace]
	, rh.[recovery] [WithRecovery]
	, rh.stop_at [StopAt]
	, rh.stop_at_mark_name [StopAtMarkName]
	, rh.stop_before [StopBefore]
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
option(recompile)