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"