Extended Events for Tracking Backup and Restore Progress


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

T-SQL Script for Tracking Backup and Restore Progress
The following code is an alternative to using Extended Events for tracking Backup and Restore Progress and provides estimated time of completion, as well. The code can also be used to track certain other potentially long duration processes including DBCC CheckDB, UpdateStatistics, and SPID KILLs. 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"