Monitor Backup And Restore Progress In Sql Server
Few scripts and examples, based on the same DMVs, of how to monitor backup and restore Progress In SQL Server 2005 and 2008.
--SCRIPT 1 SELECT session_id AS SPID , command , a.text AS Query , start_time , percent_complete , DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command IN ( 'BACKUP DATABASE', 'RESTORE DATABASE' ) --run at 11:04 --percent complete: 63.56613 --estimated completion time: 2012-04-03 12:38:55.033 --SCRIPT 3 SELECT command , s.text , start_time , percent_complete , CAST(( ( DATEDIFF(s, start_time, GETDATE()) ) / 3600 ) AS VARCHAR) + ' hour(s), ' + CAST(( DATEDIFF(s, start_time, GETDATE()) % 3600 ) / 60 AS VARCHAR) + 'min, ' + CAST(( DATEDIFF(s, start_time, GETDATE()) % 60 ) AS VARCHAR) + ' sec' AS running_time , CAST(( estimated_completion_time / 3600000 ) AS VARCHAR) + ' hour(s), ' + CAST(( estimated_completion_time % 3600000 ) / 60000 AS VARCHAR) + 'min, ' + CAST(( estimated_completion_time % 60000 ) / 1000 AS VARCHAR) + ' 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' ) --SCRIPT 3 SELECT r.session_id , r.command , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS [ETA Completion Time] , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours] , CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text, r.statement_start_offset / 2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE ( r.statement_end_offset - r.statement_start_offset ) / 2 END) FROM sys.dm_exec_sql_text(sql_handle) )) FROM sys.dm_exec_requests r WHERE command IN ( 'RESTORE DATABASE', 'BACKUP DATABASE' ) --SCRIPT 4 SELECT command , s.text , start_time , percent_complete , CAST(( ( DATEDIFF(s, start_time, GETDATE()) ) / 3600 ) AS VARCHAR) + ' hour(s), ' + CAST(( DATEDIFF(s, start_time, GETDATE()) % 3600 ) / 60 AS VARCHAR) + 'min, ' + CAST(( DATEDIFF(s, start_time, GETDATE()) % 60 ) AS VARCHAR) + ' sec' AS running_time , CAST(( estimated_completion_time / 3600000 ) AS VARCHAR) + ' hour(s), ' + CAST(( estimated_completion_time % 3600000 ) / 60000 AS VARCHAR) + 'min, ' + CAST(( estimated_completion_time % 60000 ) / 1000 AS VARCHAR) + ' 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' ) --SCRIPT 5 SELECT dmr.session_id , dmr.status , dmr.start_time , dmr.command , dmt.TEXT , dmr.percent_complete FROM sys.dm_exec_requests dmr CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN ( 'ALTER', 'Backup', 'Restore', 'DBCC', 'Rollback', 'TDE' ) --SCRIPT 6 SELECT percent_complete , ( estimated_completion_time / 1000 ) / 60 Estimated_completion_time_Mins , ( total_elapsed_time / 1000 ) / 60 Total_Elapsed_Time_Mins , DB_NAME(Database_id) DBName , * FROM sys.dm_exec_requests WHERE session_id = 129 --SCRIPT 7 USE master SELECT session_id AS [SPID] , CONVERT(VARCHAR(50), start_time, 120) AS [StartTime] , percent_complete AS [PercentageComplete] , CONVERT(VARCHAR(50), DATEADD(second, estimated_completion_time / 1000, GETDATE()), 120) AS [EstimatedCompletionTime] , command AS [Command] , a.text AS [OriginalQuery] FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'