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

Solarwinds

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

Solarwinds
, , ,
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

11 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

Leave a Reply

Menu