Script – Track progress of backup/restore task

Here is another script I frequently use to track the progress of an ongoing backup and restore operation.

SELECT command
	,s.TEXT
	,start_time
	,percent_complete
	,CAST(((DATEDIFF(s, start_time, GetDate())) / 3600) AS VARCHAR) + ' hour(s), ' + CAST((DATEDIFF(s, start_time, GetDate()) % 3600) / 60 AS VARCHAR) + 'min, ' + CAST((DATEDIFF(s, start_time, GetDate()) % 60) AS VARCHAR) + ' sec' AS running_time
	,CAST((estimated_completion_time / 3600000) AS VARCHAR) + ' hour(s), ' + CAST((estimated_completion_time % 3600000) / 60000 AS VARCHAR) + 'min, ' + CAST((estimated_completion_time % 60000) / 1000 AS VARCHAR) + ' sec' AS est_time_to_go
	,dateadd(second, estimated_completion_time / 1000, getdate()) AS est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command IN (
		'RESTORE DATABASE'
		,'BACKUP DATABASE'
		,'RESTORE LOG'
		,'BACKUP LOG'
		)