Script to find backup and restore progress in SQL Server 2005 and above versions .

Being a DBA you might get call on during midnight and backup & Restore team (in MNC's separate team will be there base on project budget) will call you and ask like my database is taking more time and may I know when it will complete .

Please execute the below script under any database because these are system defined database management views  and functions i,e DMV's & DMF's.

SELECT session_id AS SPID
 ,command
 ,a.TEXT AS Query
 ,start_time
 ,percent_complete
 ,dateadd(second, estimated_completion_time / 1000, getdate())  

AS estimated_completion_time
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'
     ) 

The above script aloso provide what query it is running .

Note: It will show any third party tools such as HP Data Protector , LiteSpeed etc information also .