Do you need to check the progress of your BACKUP/RESTORE?

If your backup (or restore) have been started by another adminitrator or by a job, you cannot use the GUI (or the Messages tab) to follow the progression.

In this case some dynamic management views can be used to track the backup/restore progress:

SELECT command,
s.text,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated 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')

If you are not interested in the command used, the s.text and view dm_exec_sql_text can be removed:

SELECT command,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Of course, you can adapt this statement to select more (or less) information.

Hope you like this blog and any comments/suggestions are welcome!

Regards,

Eduardo Rezende