How to check Backup and Restore command Progress in SQL Server?

In this Post we will discuss how to check the backup and restore command progress using below view.

select * from sys.dm_exec_requests;

Generally I like  concentrate on few things while checking on the progress of the backup and Restore command.

  1. Type of command
  2. Actual Query
  3. Start time
  4. Percentage completed
  5. Running time
  6. Estimate time to run
  7. Estimate time to complete

To get the  Actual Query , I use sys.dm_exec_sql_text view which will help to convert sql handle to text.

Below query will give info about ‘RESTORE DATABASE’, ‘BACKUP DATABASE’, ‘RESTORE LOG’, ‘BACKUP LOG’ commands.

Query:

SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + ' min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + ' min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_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')

 

 

Output:

 

backup and restore command progress

 

 

Note: Some time Estimate will vary.

Please click me for more info about Sys.dm_exec_requests view.

I hope this post help you in getting the Backup and Restore command progress,Please leave you feed back about this post  on the below comments. Thanks

Add a Comment

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