Cool Tips‎ > ‎Backup and Restore‎ > ‎

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

ċ
Get RESTORE progress.sql
(1k)
Andy Hughes,
Sep 3, 2014, 9:40 AM
Comments