Frequently you will be asked how much longer a restore will take because someone needs to do something with that specific database that is restoring right now

Of course we all know that the RESTORE DATABASE command has the STATS n option, this will give you the percentage completed. This is nice but it doesn’t tell you when it will complete and if someone else started the restore how will you know how long it will take in that case?

Fear not, here is a query that will tell you exactly how long

T-SQL
1
2
3
4
5
6
7
8
9
10
11

SELECT  
    d.PERCENT_COMPLETE AS [%Complete],
    d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
    d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
    d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
    d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
    s.text AS Command
FROM    sys.dm_exec_requests d 
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
ORDER   BY 2 desc, 3 DESC
SELECT	
    d.PERCENT_COMPLETE AS [%Complete],
    d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
    d.ESTIMATED_COMPLETION_TIME/60000	AS TimeRemainingMin,
    d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
    d.ESTIMATED_COMPLETION_TIME*0.00000024	AS TimeRemainingHours,
    s.text AS Command
FROM	sys.dm_exec_requests d 
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
ORDER	BY 2 desc, 3 DESC

Here is the output for a fairly large database restore that I started last night

%Complete ElapsedTimeMin TimeRemainingMin ElapsedTimeHours TimeRemainingHours Command
78.6186 766 208 11.03301576 2.99693760 RESTORE database SomeDB

As you can see the query returns elapsed time both in hours and minutes, time remaining both in hours and minutes and also the percentage complete

Hopefully this will help you with those nagging types…….

Related Posts