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%'
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License