SQL Server / Monitor Backup and Restore progress

Posted by Barac in SQL Monitoring, SQL Server, T-SQL on Dec 6th, 2017 | 2 comments

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

SELECT
Session_id as SPID
, Command
, query.text AS "Query Text"
, Start_Time
, Percent_Complete
, dateadd(second,total_elapsed_time/1000, getdate()) as "Total Elapsed Time"
, dateadd(second,estimated_completion_time/1000, getdate()) as "Estimated Completion Time"
FROM sys.dm_exec_requests request CROSS APPLY sys.dm_exec_sql_text(request.sql_handle) query
WHERE request.command in ('BACKUP DATABASE','RESTORE DATABASE')

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

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

Manpreet Singh says:

August 27, 2020 at 3:05 pm

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.

Reply
- Barac says:
  
  August 27, 2020 at 8:43 pm
  
  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.
  
  Reply

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Monitor Backup and Restore progress

Similar Posts:

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

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud