Skip to Content

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

To report this post you need to login first.

11 Comments

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

  1. Gobinda Ram Sharma

    Nice blog. It helped me a lot….I was performing a full backup in shared location but did not issue STATS monitoring option in the backup command…

    Regards,

    GobindaRam Sharma

    (1) 
  2. Joze Lopez

    Hola Eduardo, how this could work in SP11? I am not able to find  DM_EXEC_REQUESTS

    SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DM_EXEC_REQUESTS in schema SYS: line 6 col 10 (at pos 200)

    muito obrigado!

    (1) 
    1. Eduardo Rezende Post author

      Hola Joze Lopez,

      I just check this query on my SQL Server 2016 environment and I did not face any issue.
      Let me know if you need any help.

      Best regards,
      Eduardo

      (0) 

Leave a Reply