Get Sql Database Restore Progress by SQL

Some time when we Restore a large SQL backup file it takes a long time and SQL Popup window not show the actual progress of database restore process.

But we can find out it by the following SQL query.

SELECT
pro.PERCENT_COMPLETE AS [%Complete],
pro.TOTAL_ELAPSED_TIME/60000 AS Total_Elapsed_Time_in_Min,
pro.ESTIMATED_COMPLETION_TIME/60000 AS Total_Time_Remaining_in_Min,
pro.TOTAL_ELAPSED_TIME*0.00000024 AS Toatl_Elapsed_Time_in_Hours,
pro.ESTIMATED_COMPLETION_TIME*0.00000024 
AS Total_Time_Remaining_in_Hours,
stext.text AS Command_Text
FROM sys.dm_exec_requests pro
CROSS APPLY sys.dm_exec_sql_text(pro.sql_handle)AS stext
WHERE pro.COMMAND LIKE 'RESTORE DATABASE%'
ORDER   BY Total_Elapsed_Time_in_Min DESC, 
Total_Time_Remaining_in_Min DESC

Following is the output which I got when I tried to restore more that 10 GB backup file

Backup Restore Elapsed Time
Picture 1.1: Backup Restore Elapsed Time

How to consider the above output
— First column “%Complete” will show you the actual process of your restore progress
— Second column with show you the Total Process time
— Third column will show you Total remaining time to complete this restore process.

Thanks

Author: Ankur

Have worked primarily in the domain of Calling, CRM and direct advertisers services. My technological forte is Microsoft Technologies especially Dot Net (Visual Studio 2003, 2005, 2008, 2010 and 2012) and Microsoft SQL Server 2000,2005 and 2008 R2. My Area of Expertise is in C#. Net, VB.Net, MS-SQL Server, ASP. Net, Silverlight, HTML, XML, Crystal Report, Active Reports, Infragistics, Component Art, ComponeOne, Lead Tools etc.