Estimated Completion Time of SQL Queries (BACKUP/RESTORE)

When you restore or backup a huge database, you might be anxious to know the estimated completion time of that command.

There is a "STATS" parameter that you can use with the command, but it is not dynamic.

The following query can help you in such scenario.

select dr.session_id,      

       dr.command,

       percent_complete,

       dr.start_time,

       dateadd(mi,estimated_completion_time/60000,getdate()) EstimatedCompletionTime,

       ds.text

from  sys.dm_exec_requests dr cross apply sys.dm_exec_sql_text(dr.sql_handle) as ds

where estimated_completion_time > 0

Note that, only following SQL Commands have the percentage value populated. 

·         ALTER INDEX REORGANIZE

·         SHRINK

·         BACKUP DATABASE

·         DBCC COMMANDS

·         RECOVERY

·         RESTORE DATABASE,

·         ROLLBACK

·         TDE ENCRYPTION