Backup/Restore progress

Claus Munch on Aug 30, 2021 · 1 min read

Backup/Restore progress

Sometimes you just need to have an idea of how long a backup/restore has been running or maybe for how long it will roughly run. Here's a small script, that will give you these indications. 

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

About the author

Claus Munch has been working with SQL Server in all versions, since 2001. For more than 10 years, he has been running the national SQL Server usergroup. Claus is currently employed by Miracle 42, since 2020.