SELECT 
  session_id 
, command
, a.text AS query
, database_id --> master
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE') 

SELECT	top 100
	te.name, 
	ft.SPID,
	ft.LoginName,
	ft.Hostname,
	ft.ClientProcessID,
	ft.DatabaseName, 
	ft.FileName, 
	ft.StartTime, 
	dateadd(ms,ft.Duration/1000,ft.StartTime) [FinishTime],
	ft.ApplicationName ,
	ft.ObjectID,
	ft.Duration/1000/1000 [Duration (s)],
	ft.TextData,
	ft.RoleName,
	ft.TargetUserName,
	ft.TargetLoginName
FROM sys.traces st 
CROSS Apply ::fn_trace_gettable(reverse(stuff(reverse(st.path),1,charindex('_',reverse(st.path)),'crt.')), st.max_files) AS ft 
INNER JOIN sys.trace_events AS te ON ft.EventClass = te.trace_event_id 
WHERE 1=1
	and  te.name LIKE 'Audit Backup/Restore Event'  
	and st.is_default=1 
	and st.status=1
ORDER BY ft.StartTime desc;

;WITH RST as
(
	SELECT TOP 1000
		 ft.SPID	
		,ft.DatabaseName 
		,ft.DatabaseID      --by (full) restore database is null 
		,ft.StartTime	
		,cast(ft.TextData	 as nvarchar(max)) TextData
		,ft.ClientProcessID 
		,ft.HostName 			 
	FROM 
	            sys.traces st 
	CROSS APPLY ::fn_trace_gettable(reverse(stuff(reverse(st.path),1,charindex('_',reverse(st.path)),'crt.')), st.max_files) AS ft 
	INNER JOIN  sys.trace_events AS te ON ft.EventClass = te.trace_event_id 
	WHERE te.name LIKE 'Audit Backup/Restore Event'   
	ORDER BY ft.StartTime desc
)
, QVR as
(
	SELECT 	
	  req.command type				
	, SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1, 
	((CASE req.statement_end_offset
		WHEN -1 THEN DATALENGTH(sqltext.text)
		ELSE req.statement_end_offset
		END - req.statement_start_offset)/2) + 1) AS statement_text    
	,db_name(req.database_id) db_name
	,req.percent_complete 
	,req.estimated_completion_time 
	,req.session_id as s_id
	,req.status as req_status		
	,ses.host_name	
	,ses.host_process_id	
	,req.start_time 
	FROM 
				sys.dm_exec_requests req with(nolock)	
	INNER JOIN  sys.dm_exec_sessions ses with(nolock) on req.session_id=ses.session_id			 
	CROSS APPLY sys.dm_exec_sql_text(req.sql_handle)  AS sqltext 	
	where 1=1    
	and req.session_id <> @@SPID
)
select 
 a.SPID 
,b.type 
,(select d.dbid  from sysdatabases d where a.DatabaseName = d.name ) database_id
,a.DatabaseName 
,a.TextData 
,b.percent_complete
, RTRIM(estimated_completion_time/1000/3600)+':'+RIGHT('0'+RTRIM((estimated_completion_time/1000)%3600/60), 2)+':'+RIGHT('0'+RTRIM((estimated_completion_time/1000)%60), 2) AS TimeRemaining
 from 	
RST a join QVR b on a.TextData = b.statement_text 
and a.StartTime = b.start_time 
and a.SPID = b.s_id 
and a.ClientProcessID = b.host_process_id 

SELECT 	
 req.command type				
, SUBSTRING
	(sqltext.text
	,req.statement_start_offset/2+1
	,(
	    CASE req.statement_end_offset
		WHEN -1 THEN DATALENGTH(sqltext.text)
		ELSE req.statement_end_offset
		END - req.statement_start_offset
		)/2 + 1
	) AS statement_text    
,req.percent_complete 
,RTRIM(estimated_completion_time/1000/3600)+':'+RIGHT('0'+RTRIM((estimated_completion_time/1000)%3600/60), 2)+':'+RIGHT('0'+RTRIM((estimated_completion_time/1000)%60), 2) AS TimeRemaining
,req.session_id as s_id
,req.status as req_status		
,ses.host_name
,ses.host_process_id	
,req.start_time 
,lck.resource_database_id  database_id
,db_name(lck.resource_database_id) database_name
FROM 
     sys.dm_exec_requests req with(nolock)	
JOIN sys.dm_exec_sessions ses with(nolock) on req.session_id=ses.session_id			 	
JOIN sys.dm_tran_locks	 lck with(nolock) on req.session_id=lck.request_session_id 
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle)  as sqltext 
where 1=1    
and req.session_id <> @@SPID
and lck.request_mode = 'X'
and lck.request_owner_type = 'EXCLUSIVE_TRANSACTION_WORKSPACE'
and sqltext.text like '%RESTORE DATABASE%'