Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
[MSSQL] Checks Status of a DB Restore
declare @sql_text char(255);
declare @total_time_so_far int;
declare @total_percent float;
declare @seconds_per_percent float;
declare @total_time time;
declare @time_left time;
SELECT @sql_text = sqltext.TEXT,
@total_time_so_far = req.total_elapsed_time,
@total_percent = req.percent_complete
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where
sqltext.TEXT like '%restore%' and sqltext.TEXT not like '%select sqltext.TEXT%'
SELECT @seconds_per_percent = (@total_time_so_far / 1000) / @total_percent
SELECT @total_time = CONVERT(TIME, DATEADD(SECOND, @seconds_per_percent * 100,0), 108)
SELECT @time_left = CONVERT(TIME, DATEADD(second, @seconds_per_percent * (100-@total_percent),0), 108)
SELECT @total_time_so_far as 'Total Time So Far (in ms)', @total_percent as '% Completed', @seconds_per_percent as 'Seconds per %', @total_time as 'Estimated total time', @time_left as 'Estimated remaining time'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment