0
(0)

Performing 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:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.