“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!

The post How to Get Estimated Completion Time of SQL Server Database Backup OR Restore appeared first on .