If there is ever a problem and we need my company needs to recover data from the database backup, the first question is always, when will the restore or backup be done? Microsoft has kindly provided a graphic which will show 10% increments of how the progress is going, but that does not answer the question “How can I find out how much time a SQL server database restore or backup will take while it is running?”.

A recent blog post by Adam Rush had a great SQL command you can run to get the current status:

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


This short SQL Script has helped me provide a better estimate of when the restoration will be complete, than the built in percentage provided by Microsoft.