How long will this sql server restore take?

Have you ever wanted to know how long its going to take to complete  a restore once you issued the restore T-SQL command? The DMV’s can actually give you this information.  My script to complete this task is included below.

[code language=”sql”]
/* Query used to find status of a backup. */
SELECT sysdb.NAME,
dmv.PERCENT_COMPLETE AS [PercentComplete],
dmv.TOTAL_ELAPSED_TIME/60000 AS [Elapsed_Time_in_Minutes],
dmv.ESTIMATED_COMPLETION_TIME/60000 AS [Time_Remaining_in_Minutes],
[Individual Query] = SUBSTRING (qt.text,  dmv.statement_start_offset/2, (CASE WHEN dmv.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE dmv.statement_end_offset END – dmv.statement_start_offset)/2),
[Parent Query] = qt.text
FROM MASTER..SYSDATABASES sysdb
inner join sys.dm_exec_requests dmv on sysdb.DBID=dmv.DATABASE_ID AND dmv.COMMAND LIKE ‘%restore%’
CROSS APPLY sys.dm_exec_sql_text(dmv.sql_handle)as qt
ORDER BY 2 desc, 3 desc
[/code]