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?
Reference: Pinal Dave (http://blog.SQLAuthority.com), Original Source.Â
13 Comments. Leave new
If you remove the “command like” and just focus on “where percent_complete > 0” you’ll also get info about DBCC commands
Sure. Makes sense.
Dear Pinal Dave
i need to say one thing to appreciate your blog post. they are clear/detailed enough to understand and short enough to read.
Thank you and wish you all the best.
Thanks bt
When I am facing any issues and willing for a solution, then first though is Pinal blog . It very good and best solution we getting from your blog.
wow. that’s nice to hear Shakeel
Any idea on how to target a specific db that is restoring? THat query shows all dbs that are restoring on a db. On a busy test db there could be a dozen or so and I want to target the one that I am restoring whose name I know.
add a where clause like this: WHERE CHARINDEX(‘{yourdatabasename}’, r.command) > 0
Doh! Thank you!
Hi Pinal,
Thanks for the query, this really showed the status of the hours completed of the restore initiated,
with a database sized about more than 1 TB which was compressed to 574 GB. But the query that you shared didn’t show the percentage correctly.
This below given command populated me the percentage completion in float.
select start_time, status, blocking_session_id
, wait_type, wait_time, last_wait_type, wait_resource
, percent_complete, estimated_completion_time
,total_elapsed_time, reads, writes, cpu_time
from sys.dm_exec_requests
where command = ‘RESTORE DATABASE’
Pinal, please help me to understand whether is the right query that I can trust upon on SQL Server 2017 (running on Azure VM).
Thank You,
Regards,
Guruprasad
not sure why there was a difference. Both are using same base DMV. I have some extra calculation which he is not having.
Here is mine, I usually run it to check whether my AGs are in the middle of a sync operation, but it also shows info on running DBCC, BACKUP and RESTORE commands:
SELECT r.session_id
,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS SessionElapsedMins
,DB_NAME(r.database_id) AS DB
,USER_NAME(r.[user_id]) AS UserName
,r.command AS CurrentCommand
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS CurrentCommandPercentComplete
,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS CurrentCommandETA
,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS CurrentCommandETA_Mins
,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS CurrentCommandETA_Hours
,CONVERT(VARCHAR(8000), (
SUBSTRING(t.[text], r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = – 1
THEN 8000
ELSE (r.statement_end_offset – r.statement_start_offset) / 2 + 2
END)
)) AS CurrentCommandStatement
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
WHERE percent_complete 0.00;
Thank you, it is very useful. I have 2.8TB backup and I don’t know when it will complete. It gives me a good picture and I don’t need to put my eyes on it all the time.