Monitoring SQL Server Restore Progress with sys.dm_exec_requests

When restoring a large database in SQL Server, one of the most common questions from administrators, project teams, and business users is:

“How much longer will it take?”

SQL Server does not always give clear progress feedback in the standard restore window, especially when you are managing restores through scripts, SQL Server Management Studio, or remote sessions. That is where Dynamic Management Views (DMVs) become very useful.

A simple query like the one below can help you monitor active restore operations in real time:

select 
    text,
    r.percent_complete,
    r.estimated_completion_time,
    *
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.command like '%RESTORE%'

Why this query is useful

This script helps DBAs identify currently running restore commands and check their progress. It is especially valuable during:

  • full database restores
  • differential restores
  • transaction log restores
  • point-in-time recovery operations
  • large migration or DR recovery activities

Instead of guessing whether the restore is still running or stuck, this query provides live visibility into what SQL Server is doing.

Breaking down the script

Let’s look at each part of the query.

1. sys.dm_exec_requests

from sys.dm_exec_requests r

sys.dm_exec_requests is a SQL Server DMV that shows information about each request currently executing inside SQL Server.

For a restore operation, this DMV can show:

  • the session executing the restore
  • the command type
  • percent complete
  • estimated completion time
  • wait information
  • start time
  • CPU and I/O statistics
  • blocking and session details

In this script, the DMV is aliased as r.

2. sys.dm_exec_sql_text(r.sql_handle)

cross apply sys.dm_exec_sql_text(r.sql_handle) t

This part retrieves the SQL text associated with the running request.

sys.dm_exec_requests gives metadata about the request, but not always the full command text. By using:

sys.dm_exec_sql_text(r.sql_handle)

you can see the actual restore statement that is running, such as:

RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\MyDB.bak'

The cross apply joins each active request to its SQL text, and the result is aliased as t.

That is why the query can return the text column.

3. r.percent_complete

r.percent_complete

This column shows how much of the restore operation has completed, expressed as a percentage.

For example:

  • 10.5 means the restore is 10.5% complete
  • 75.0 means the restore is 75% complete
  • 100.0 means the operation is complete or nearly complete

This is one of the most useful fields for tracking long-running restores.

4. r.estimated_completion_time

r.estimated_completion_time

This column shows SQL Server’s estimate of how much time remains before the restore finishes.

Important point:
The value is returned in milliseconds, not minutes or seconds.

So if the value is:

  • 60000 = about 1 minute
  • 300000 = about 5 minutes
  • 3600000 = about 1 hour

This estimate is helpful, but it should be treated as an approximation. The remaining time can change depending on:

  • disk performance
  • network throughput
  • backup file location
  • compression
  • workload on the server
  • restore phase currently in progress

5. text

select text, ...

This returns the SQL text from sys.dm_exec_sql_text, allowing you to see the restore command that is running.

This is useful when multiple restore operations or maintenance tasks are happening, because it helps you confirm exactly which restore command is being executed.

6. *

select text, r.percent_complete, r.estimated_completion_time, *

The asterisk returns all available columns from the joined result set.

This can be helpful for troubleshooting because it exposes additional details such as:

  • session_id
  • status
  • command
  • start_time
  • blocking_session_id
  • wait_type
  • wait_time
  • last_wait_type
  • cpu_time
  • total_elapsed_time

However, in production use, returning * may produce too much output. For a cleaner monitoring query, many DBAs prefer to explicitly list only the required columns.

7. Filtering only restore activity

where r.command like '%RESTORE%'

This filter ensures the query returns only requests related to restore operations.

Examples may include:

  • RESTORE DATABASE
  • RESTORE LOG
  • RESTORE VERIFYONLY

This makes the output cleaner by excluding unrelated requests running on the server.

What the result tells you

When you run this query during an active restore, you can quickly answer:

  • Is the restore still running?
  • What exact restore command is executing?
  • What percentage has completed?
  • How long might it take to finish?
  • Is the session waiting on a resource?
  • When did it start?

This is extremely useful during disaster recovery, production cutovers, test refreshes, and backup validation work.

Practical example

Suppose you are restoring a 2 TB production database to a DR server. The business team is asking for updates every 15 minutes.

By running this query, you may see something like:

  • percent_complete = 42.8
  • estimated_completion_time = 5400000

This tells you the restore is about 43% complete, with around 90 minutes estimated remaining.

That gives you much better visibility than simply saying, “the restore is still running.”

Recommended improved version

For day-to-day monitoring, a cleaner version of the query is often better:

select
    r.session_id,
    r.command,
    r.status,
    r.start_time,
    r.percent_complete,
    dateadd(ms, r.estimated_completion_time, getdate()) as estimated_finish_time,
    r.estimated_completion_time / 1000 / 60 as est_minutes_remaining,
    t.text as sql_text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.command like '%RESTORE%';

Why this version is better

It adds:

  • session_id for session tracking
  • status to see whether it is running or suspended
  • start_time to know when it began
  • estimated finish time as a date/time
  • estimated minutes remaining
  • a cleaner alias for the SQL text

This makes the output easier to explain to technical and non-technical stakeholders.

Things to keep in mind

There are a few limitations when using this query:

1. Estimated time is not always exact

SQL Server calculates the estimate dynamically. It can go up or down during the restore.

2. Some restore phases behave differently

Certain parts of a restore may appear slower or faster than others, especially on large systems.

3. Permissions are required

To query sys.dm_exec_requests and related DMVs, your login typically needs appropriate server-level permissions, such as VIEW SERVER STATE or equivalent depending on SQL Server version and configuration.

4. SELECT * is not ideal for reporting

Using * is fine for ad hoc troubleshooting, but for dashboards or shared scripts, a cleaner explicit column list is better.

Best use cases for this script

This script is ideal for:

  • DBAs monitoring restores during maintenance windows
  • project teams managing database refreshes
  • DR teams validating recovery timelines
  • consultants performing migrations
  • support teams providing status updates during incidents
Conclusion

This query is a simple but powerful way to monitor SQL Server restore activity in real time. By combining sys.dm_exec_requests with sys.dm_exec_sql_text, you can see not only that a restore is running, but also how far it has progressed and how long it may take to finish.

For any DBA or database consultant, this is a practical script worth keeping ready in your toolkit.