How to Get Status of Running Backup and Restore in SQL Server? – Interview Question of the Week #113

Question: How to Get Status of Running Backup and Restore in SQL Server?

How to Get Status of Running Backup and Restore in SQL Server? - Interview Question of the Week #113 runningbackup-800x362

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

SQL Backup, SQL Restore, SQL Scripts, SQL Server
Previous Post
Why to Use SQL Server Configuration Manager Over Services applet (services.msc)? – Interview Question of the Week #112
Next Post
How to Find How Many Rows Each Query Returned Along with Execution Plan? – Interview Question of the Week #115

Related Posts

14 Comments. Leave new

  • Wilfred van Dijk
    March 12, 2017 4:10 pm

    If you remove the “command like” and just focus on “where percent_complete > 0” you’ll also get info about DBCC commands

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Guruprasad Balaji
    January 6, 2018 11:58 pm

    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

    Reply
    • not sure why there was a difference. Both are using same base DMV. I have some extra calculation which he is not having.

      Reply
  • 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;

    Reply
  • Xiaogang Zheng
    October 19, 2020 9:43 pm

    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.

    Reply
  • We may need an update on the syntax for SQL 2022 – I have a NULL in the percent complete column.

    Reply

Leave a Reply