Extended Events for Tracking Backup and Restore Progress


 * http://www.sqlservercentral.com/articles/Backup+and+restore/148817/

SELECT DBName             = DB_NAME(r.database_id) ,Command           = r.command ,SessionStartDT    = CONVERT(CHAR(16),r.start_time,120) ,CurrentDT         = CONVERT(CHAR(16),GETDATE,120) ,EstCmdEndDT       = CONVERT(CHAR(16),DATEADD(ss,estimated_completion_time/1000, GETDATE),120) ,CmdTimeToGo       = CONVERT(VARCHAR(10),DATEDIFF(hh,0,DATEADD(ss,estimated_completion_time/1000, 0))) + RIGHT(CONVERT(CHAR(8),DATEADD(ss,estimated_completion_time/1000, 0),108),6) ,CmdPctComplete    = r.percent_complete ,SessionDuration   = CONVERT(VARCHAR(10),DATEDIFF(hh,0,GETDATE-r.start_time)) + RIGHT(CONVERT(CHAR(8),GETDATE-r.start_time,108),6) ,UserName          = USER_NAME(r.[user_id]) ,SPID              = r.session_id ,BlockingSPID      = r.blocking_session_id ,Query             = (SELECT '--' + CHAR(10) + a.[text] + CHAR(10) AS [processing-instruction(QueryCode)] FOR XML PATH(''),TYPE) 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','DBCC CHECKDB','KILL','UPDATE STATISTICS') ORDER BY DBName

"Code provided by Jeff Moden (MS SQL Server MVP) via SQLServerCentral.com"