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.
/* 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