Monitoring SQL Server Backup and Restore Progress

Managing database backups and restores is a critical aspect of SQL Server administration. When dealing with large databases, it becomes essential to track the progress of ongoing backup and restore operations to estimate completion time and monitor performance. This blog post provides a structured SQL script to monitor these operations effectively.

Why Monitor Backup and Restore Progress?

• Ensures visibility into long-running operations

• Helps estimate completion time for better planning

• Identifies potential performance bottlenecks

• Prevents unnecessary disruptions by avoiding premature interventions

SQL Script to Monitor Backup and Restore Progress Use the following SQL script to track the percentage completion, elapsed time, and estimated completion time of backup and restore operations in SQL Server:

SELECT

    r.session_id AS [Session_Id],

    r.command AS [Command],

    CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete],

    GETDATE() AS [Current Time],

    CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time],

    CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min],

    CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min],

    CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated 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)

    )) AS [Statement Text]

FROM sys.dm_exec_requests r

WHERE r.command LIKE 'RESTORE%'

   OR r.command LIKE 'BACKUP%'

   OR r.command LIKE 'DbccFilesCompact'

   OR r.command LIKE 'DbccSpaceReclaim';