Question: How to Get Status of Running Backup and Restore in SQL Server?
Answer: One of the reasons I started my blog long ago was to have a repository of scripts which can be very useful by DBA during daily troubleshooting. There are many times when DBA wants to check the progress of a backup or a restore activity which is happening on the server. By combining various commands from the internet, I have come up with below script which can give us a summary of current backups and restores which are happening on the 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) 'Statement text' FROM sys.dm_exec_sql_text(sql_handle) )) FROM sys.dm_exec_requests r WHERE command like 'RESTORE%' or command like 'BACKUP%'
Recently I found this script very handy while I was doing performance tuning exercise for a customer. Suddenly we had observed that one of their drives had more I/O and when we ran above script we figured out that there is a backup operation going on the system.
Do you have any similar interesting script which you can share via comments?