How to monitor backup and restore progress in SQL Server
T-SQL Scripts Backup, Restore, SQL server, TSQLPerforming backup and restore operation is very common task in DBA's life and as part of this operation DBA's always want to monitor the progress of backup/restore operation and below command can be very much useful to monitor the status.
SELECT er.session_id
, er.command
, a.text AS Query
, er.start_time
, er.percent_complete
, CONVERT(VARCHAR(20), DATEADD(ms, er.estimated_completion_time , GETDATE()), 20) AS [Estimated_Completion_Time]
, CONVERT(NUMERIC(10, 2), er.estimated_completion_time / 1000.0 / 60.0) AS [ Estimated_Min_to_complete]
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) a
WHERE er.command in ('BACKUP DATABASE','RESTORE DATABASE')
Output:
