658
Monitoring the progress of SQL Server backup and restore operations is essential to ensuring the availability and reliability of critical data. Using the sys.dm_exec_requests dynamic management view and the sys.dm_exec_sql_text function, database administrators can retrieve information about backup and restore operations currently running on the server, such as the percentage complete, the estimated completion time, and the elapsed time. By monitoring backup and restore progress using T-SQL scripts that join these views with the CROSS APPLY operator, database administrators can proactively identify any issues and take appropriate action to prevent data loss.
T-Sql
--Script to monitor the progress of a sql server backup or restore activity
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')
--this where condition is filtering for both bacup and restore activity. just alter this line accordingly.
Note
For further information on this post, kindly refer to the original source provided in the link below.
Source: mssqltips
