Database Restore Progress in SQL Server
This post will takes you through you monitoring Database Restore Progress in SQL Server. Here we have an useful script which can help us in the process of restoration of databases. As we are restoring the databases on daily basis of size ranging from 1 GB to 1TB files ,we would like to know the exact percentage completed , and estimated time to complete. The below script retrieves those details which can help us in further proceedings.
T-SQL Script to Monitor Database Restore Progress in SQL Server:
USE MASTER
GO
SELECT
Command,
s.Text AS Query,
start_time AS StartTime,
percent_complete AS Percentage_Completed,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600)asvarchar)+' hour(s), '
+CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 asvarchar)+'min, '
+CAST((DATEDIFF(s,start_time,GetDate())%60)asvarchar)+' sec'as Running_Time,
CAST((estimated_completion_time/3600000)asvarchar)+' hour(s), '
+CAST((estimated_completion_time %3600000)/60000 asvarchar)+'min, '
+CAST((estimated_completion_time %60000)/1000 asvarchar)+' sec'as Est_Time_To_Go,
DATEADD(second,estimated_completion_time/1000,getdate())as Est_Completion_Time
FROM SYS.DM_EXEC_REQUESTS R
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE
R.Command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
We have initiated a restore and ran this query it shows the status as below
After a few minutes
