SQL Server / Monitor Backup and Restore progress

Recently I had a situation on QA environment where Backup and Restore took a long time to finish.

It was HA environment with two nodes in Always On High Availability Group, so backup and restored occurred over network shared location.

Drives were slow, database was huge and network latency not the best  so it took a long time to add database to the availability group.

To follow up backup or restore progress with start time, total elapsed time and estimated completion time in similar situation you can use following script

 

You can follow up percent complete in results pane

 

Be aware that the restore can stuck close to the end around 99%, if database is large it may take some additional times (even couple hours) for recovery to complete.

To understand better how restore and recovery process works you can read this Microsoft article
Understanding How Restore and Recovery of Backups Work in SQL Server

 

 

Similar Posts:

2 Responses to “ “SQL Server / Monitor Backup and Restore progress”

  1. Manpreet Singh says:

    If the restore is happening from Commvault backup then how can we monitor the restore progress???

    Tried all above select query but didn’t work.

    • Barac says:

      Hi Manpreet,
      To be honest I would expect to see those requeste since sys.dm_exec_requests DMV show us the requests being made from connected users and applications.
      This particular query is using sys.dm_exec_requests DMV to identifies the current ‘BACKUP DATABASE’ and ‘RESTORE DATABASE’ type of command that is being processed, at the same time I am using sys.dm_exec_sql_text to retrive SQL text of the request.

      I am filtering command on ‘BACKUP DATABASE’ and ‘RESTORE DATABASE’, maybe to try without to see all the requests.

Leave a Reply

Your email address will not be published. Required fields are marked *