If you've
ever started a database backup or restore and then sat there wondering how much
longer it'll take, you already know the problem. A large database can leave you staring at a spinning cursor with no
idea whether you're waiting one more minute or another hour.
The good
news is that when a backup or restore is running, SQL Server already knows
roughly when it expects to finish. You just have to ask for it. This query does
exactly that, and the column that matters most is estimated_completion_time.
Run this in
a new query window while your backup or restore is going, and you'll get a row
back for each active operation, along with a real clock time for when SQL
Server expects to be done.
The script
SELECT
session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
The star
of the show: estimated_completion_time
This is the
column that turns a nerve-wracking wait into something you can actually plan
around.
Inside
sys.dm_exec_requests, SQL Server exposes an estimated_completion_time value,
but the raw number is the estimated milliseconds remaining. On its own, that's
not very useful. Nobody wants to do mental math on "4,380,000
milliseconds" to figure out when they can go to lunch.
That's what
this part of the query fixes:
dateadd(second, estimated_completion_time/1000, getdate())
It divides
the milliseconds by 1000 to get seconds, then adds those seconds to the current
time with getdate(). The result is an actual finish time, like 2026-07-23
14:52:10, instead of a raw count you have to interpret. You look at it and
immediately know when the operation should wrap up.
The key
thing is that this value is live. SQL Server recalculates it as the operation
runs, so each time you execute the query you get a fresh estimate based on the
work done so far. Run it, wait a bit, run it again, and you'll watch the
projected finish time settle down as the operation progresses. That's your
progress, right there in SSMS, without any external tool.
The
percent_complete column tells you how far along you are; the
estimated completion time tells you the one thing people actually care about,
which is when this will be done.
You'll also
need VIEW SERVER STATE permission to see this, since it reads from
sys.dm_exec_requests and sys.dm_exec_sql_text. Most DBAs already have it, but a
restricted login might not.
And if you
want a running view, just keep hitting execute every few seconds. Watching that
projected finish time creep closer is a lot less stressful than staring at a
blank screen wondering if anything is happening at all.
Wrapping
up
This is one
of those small scripts worth keeping in your snippets folder. When you're
running a backup or restore, it lets you see the progress directly in SQL
Server and, more importantly, tells you when the operation will actually
finish, so you can plan around it instead of guessing.
