— Check progress of any running BACKUP or RESTORE operations
SELECT
r.session_id,
r.command, — BACKUP DATABASE, RESTORE DATABASE, etc.
r.percent_complete, — Progress percentage
r.start_time, — When the operation started
r.estimated_completion_time / 1000 AS est_completion_seconds,
DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS est_completion_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
r.wait_type,
r.wait_time / 1000 AS wait_seconds,
r.last_wait_type,
t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN (‘BACKUP DATABASE’, ‘BACKUP LOG’, ‘RESTORE DATABASE’, ‘RESTORE LOG’, ‘RESTORE HEADERONLY’)
ORDER BY r.start_time;