Get Restore Progress

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 3 Sep 2014

Description

Restoring a production database can either be a routine action (when creating or updating development databases) or a problem resolution for a production system. In both cases I've found it to be annoying that there is no built-in mechanism in SQLEM for easily determining how much of the restore process is complete.

This code produces a compact report detailing progress so far and ETA for all database restores on a server. I've used this many times now and have found it to be very accurate.

Code

Get list of in-progress restores on the server

-- plus elasped time and ETA

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)

)

FROM sys.dm_exec_requests r

WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')