Skip to Content

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

/wp-content/uploads/2013/10/bak1_294403.png

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

/wp-content/uploads/2013/10/bak2_294452.png

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

11 Comments
You must be Logged on to comment or reply to a post.