Check SQL Server backup and restore progress

Sometimes when I run backup or restore operations on an MS SQL server using a maintenance plan or a simple script, I have no information about the progress. In order to check the percent complete, time spent, and remaining time, I use such a simple command:

SELECT reqests.session_id, 
    db_name = db_name(reqests.database_id),
    reqests.status,
    reqests.command,
    reqests.percent_complete,
    reqests.start_time,
    elapsed_min = CONVERT(NUMERIC(6, 2), 
        reqests.[total_elapsed_time] / 1000.0 / 60.0),
    remaning_eta_min = CONVERT(NUMERIC(6, 2), 
        reqests.[estimated_completion_time] / 1000.0 / 60.0)
FROM sys.dm_exec_requests as reqests WITH(NOLOCK)
WHERE command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

It gives me all the essential information I need – the database name, command that is taking place right now, percentage, time-related information. The above script is not limited to backup or restore operations – feel free to adjust for your needs 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *