How to track the progress of backup/ restore of a database?

Problem: How to track the progress of backup/ restore of a database?

Solution:  sys.​dm_exec_requests

In most cases, where you want to track the progress of the restore, like the duration remaining and duration elapsed to backup a database, because sql server management studio only shows what percentage of backup or restore has been done with stats. There is no actual time remaining or time related metrics.

We can query the SQL Server current executions using the dynamic management view of execution requests.

Example:

In this example, I would like to show we can track the time remaining to finish a backup.

To know more about how to select text of a query, please go through the following article. Extract text of a sql query session

Query:      

  use master

       go

       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')

Results:

command	text	start_time	percent_complete	running_time	est_time_to_go
RESTORE DATABASE	RESTORE DATABASE [DB	2017-06-30 15:58:38.127	0	0 hour(s), 0min, 1 sec	0 hour(s), 0min, 0 sec