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