Author:  ChillyDBA

Date:      3 Sep 2014

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.

-- 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')