Script: How Long Until My SQL Server Backup/Restore Completes?

Ding!  Your desktop IM client chimes. “Any idea how long my SQL Server backup/restore will take to complete? I think it may be stuck.” 

Someone has started a database restore (or backup) using a T-SQL command. Now he wants to know how long the process will take to complete. He’s gone to the bathroom and even gotten a coffee, yet it’s still running. 

If he had used the WITH STATS option for either the BACKUP DATABASE or RESTORE DATABASE command, the percentage processed would have been piped to the Messages tab of Management Studio a little at a time. 10 percent processed. 20 percent processed. And so on as shown below.

BACKUP DATABASE wideworldimporters TO DISK = 'c:\temp\wwi.bak' WITH STATS;

SQL Server backup/restore information using DMVs

Still, that doesn’t provide an estimate of how long the process is likely to run. Fortunately, the Dynamic Management Views in SQL Server allow us to gather and report backup and restore estimates.

Here’s a script that I’ve found useful. It’s an amalgamation of a script I found on StackOverflow and my own handiwork. As always, this is for your reference only and no guarantees are implied.

SELECT SERVERPROPERTY('ServerName') AS [Instance],
   reqs.session_id,
   sess.login_name,
   reqs.command,
   CAST(reqs.percent_complete AS NUMERIC(10, 2)) AS [Percent Complete],
   CONVERT(VARCHAR(20), DATEADD(ms, reqs.estimated_completion_time, GETDATE()), 20) AS [Estimated Completion Time],
   CAST(reqs.total_elapsed_time / 60000.0 AS NUMERIC(10, 2)) AS [Elapsed Minutes],
   CAST(reqs.estimated_completion_time / 60000.0 AS NUMERIC(10, 2)) AS [Estimated Remaining Time in Minutes],
   CAST(reqs.estimated_completion_time / 3600000.0 AS NUMERIC(10, 2)) AS [Estimated Remaining Time in Hours],
   CAST((
     SELECT SUBSTRING(text, reqs.statement_start_offset/2,
                CASE
                WHEN reqs.statement_end_offset = -1
                THEN 1000
                ELSE(reqs.statement_end_offset-reqs.statement_start_offset)/2
                END)
     FROM sys.dm_exec_sql_text(sql_handle)) AS VARCHAR(1000)) AS [SQL]
FROM sys.dm_exec_requests AS reqs
 JOIN sys.dm_exec_sessions AS sess ON sess.session_id = reqs.session_id
WHERE command IN('RESTORE DATABASE', 'BACKUP DATABASE');

The script returns the following information:

• Instance Name
• Session Id 
• Login Name
• Command 
• Percent Complete
• Estimated Completion Time
• Elapsed Time in Minutes
• Estimated Remaining Time in Minutes
• Estimated Remaining Time in Hours
• The Complete Command Used to Start Backup/Restore
  

Since this script uses Dynamic Management Views, it won’t work on ancient versions of SQL Server. I’ve tested it on SQL Server 2008 R2 and forward on the Windows version. I haven’t tried it on SQL Server 2017 running on Linux. 

Hopefully, you’ll find this script handy. Here are a few more that you may find helpful.

• High Availability and Disaster Recovery in SQL Server
• How to Create SQL Server 2019 Failover Clustered Instances in Azure
• Vollgar: 6 Scripts to Help Review Your SQL Servers

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.