How to Get Estimated Completion Time of SQL Server Database Backup OR Restore -

How to Get Estimated Completion Time of SQL Server Database Backup OR Restore

DHARMENDRA KESHARIDecember 09, 2017SQL LINUX, SQL WINDOWS7 comments

“How much time SQL Server is going to take to complete the database Backup or Restore” – This is one of the very common questions DBAs face in day to day life when they are performing database refresh activity, database migration activity, or any adhoc activity where DBA may want to have a DB backup before the activity takes place.

In this tip, we will explore a very simple script to get the estimated amount time which SQL Server is going to take to complete database Backup or Restore. You may find the below script very useful when we are dealing with the very large database.

SELECT 
	dmr.session_id,
	dmr.command,
	CONVERT(NUMERIC(6,2),dmr.percent_complete)AS [Percent Complete],
	CONVERT(VARCHAR(20),DATEADD(ms,dmr.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
	CONVERT(NUMERIC(10,2),dmr.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
	CONVERT(NUMERIC(10,2),dmr.estimated_completion_time/1000.0/60.0) AS [ETA Min],
	CONVERT(NUMERIC(10,2),dmr.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
	,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,dmr.statement_start_offset/2,	
								   CASE WHEN dmr.statement_end_offset = -1 THEN 1000 
								   ELSE (dmr.statement_end_offset-dmr.statement_start_offset)/2 END) 
							FROM sys.dm_exec_sql_text(sql_handle)
							)
					) [sqltxt]
FROM sys.dm_exec_requests dmr WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

This is how the output looks like;

Hope, you find this script useful to answer the question.

Keep Learning!

DHARMENDRA KESHARI

7 thoughts on “How to Get Estimated Completion Time of SQL Server Database Backup OR Restore”

gerald December 11, 2017 at 8:02 am

yes, great – and I’m using it since ages… slightly adapted

Reply
- Dharmendra December 20, 2017 at 4:11 am
  
  Thanks Gerald! glad to hear that 🙂
  
  Reply
Erick muchina December 20, 2017 at 3:17 am

The materials are very useful

Reply
- Dharmendra December 20, 2017 at 4:11 am
  
  Thank you Erick for your valuable feedback!
  
  Reply
Anne December 21, 2017 at 5:45 pm

Thanks for the scripts. But I run on my a couple of servers, and returns no record.
Does this only work on manually run backup or restore?
I do have nightly backup, it does not shown.

Reply
Anne December 21, 2017 at 6:17 pm

I did not see the comments I posted. If it is a duplicated, please pardon me.
I ran the scripts, but nothing returned on a couple of servers. I do have backup scheduled nightly.
Does this script show the nightly backup completion time? Thanks

Reply
- Dharmendra December 21, 2017 at 11:26 pm
  
  Thanks Anne for your input! It works for all kind of backups/restore like the backup is being taken by SQL Job or any other tool like DPM, LiteSpeed etc. It only shows when backup or restore is running on the server. When you ran the script, was there backup or restore running on the server?
  
  Reply