_Beetlejuice (11/17/2012)
This query will tell you how long the restore will take in seconds:SELECT Percent_Complete, Estimated_Completion_Time / 1000 as [CompletionTimeSeconds]
FROM sys.dm_exec_requests
WHERE Command LIKE '%RESTORE%'
It'll tell you how long the restore is estimated to take, which may be very different from the time it actually does take.
One reason I can think of, the last portion of a restore is to run crash recovery on the newly restored database, if the log is badly fragmented or there were lots of open transactions at the time of the backup, that recovery process may take a long time. From what I can tell, the progress (STATS = 10) is the percentage of the data copied (which can be easily calculated), not the percentage of time (which is incredibly difficult to estimate correctly)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability