DBA Scripts: Get Backup Restore Progress

June 13, 2026

–

Part of the DBA-Tools Project.

A large restore has been running for twenty minutes. Is it about to finish, or is it barely a quarter done? SSMS won’t tell you unless you started it interactively and left that session open, and a restore kicked off from a script, a job, or someone else’s session gives you nothing to watch by default.

The same question applies to a full backup of a multi-terabyte database running during a maintenance window with a hard deadline. You need to know whether it will finish in time, not just that it’s “still running.”

This script queries SQL Server’s own progress tracking for active backup and restore operations and turns it into a running time, an estimated time remaining, and an estimated completion clock time, so you’re not guessing.

Why Backup Restore Progress Matters

SQL Server tracks percent_complete for backup and restore operations internally, in sys.dm_exec_requests, but it’s not visible anywhere unless you go looking for it:

A restore during a disaster recovery scenario is exactly the moment you most need an accurate time estimate, and exactly the moment guessing is most costly
A backup running up against a maintenance window deadline needs a real answer to “will this finish in time,” not a hopeful one
Long-running DBCC space reclaim operations (DbccSpaceReclaim, DbccFilesCompact) surface here too, since they report progress the same way
Anyone who didn’t personally start the operation, an on-call DBA picking up someone else’s restore mid-flight, has no other easy way to see how far along it is

Without this, the honest answer to “how much longer” is often “I don’t know,” which is a bad thing to tell someone during an incident.

When to Run This Script

During a live restore, to get a real time estimate instead of guessing
During a large full backup running close to a maintenance window deadline
When picking up an in-progress operation someone else started
As a quick check before deciding whether to wait or escalate

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-BackupRestoreProgress
Category    : performance-troubleshooting
Purpose     : Show active backup/restore progress and estimated completion for long-running operations.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-backup-restore-progress/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT 
    er.command AS command,
    est.text AS sql_text,
    er.start_time AS start_time,
    er.percent_complete AS percent_complete,
    CAST(((DATEDIFF(SECOND, er.start_time, GETDATE())) / 3600) AS VARCHAR)
        + ' hour(s), ' 
        + CAST((DATEDIFF(SECOND, er.start_time, GETDATE()) % 3600) / 60 AS VARCHAR)
        + ' min, ' 
        + CAST((DATEDIFF(SECOND, er.start_time, GETDATE()) % 60) AS VARCHAR)
        + ' sec' AS running_time,
    CAST((er.estimated_completion_time / 3600000) AS VARCHAR)
        + ' hour(s), ' 
        + CAST((er.estimated_completion_time % 3600000) / 60000 AS VARCHAR)
        + ' min, ' 
        + CAST((er.estimated_completion_time % 60000) / 1000 AS VARCHAR)
        + ' sec' AS estimated_time_remaining,
    DATEADD(SECOND, er.estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE er.command IN
(
    'RESTORE DATABASE',
    'BACKUP DATABASE',
    'RESTORE LOG',
    'BACKUP LOG',
    'DbccSpaceReclaim',
    'DbccFilesCompact'
);

The script returns one row per active backup, restore, or DBCC space-reclaim operation, with a live percent complete and estimated completion time. It returns no rows when nothing matching is currently running.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check progress of any active backup or restore operation:
.\run.ps1 Get-BackupRestoreProgress

# To run against a remote sql server:
.\run.ps1 Get-BackupRestoreProgress -ServerInstance SQLSERVER01

This script lives in the repo at:

Example Output

Real output, captured mid-flight during a genuine backup of a purpose-built ~3GB lab database, deliberately run without compression to slow it down enough to catch. At the moment of capture the backup was 14 seconds in, just over 62% complete, with SQL Server estimating 8 more seconds to go. This is a narrow, honest window; catching this output requires querying while an operation is actually running, since the moment it finishes, the row disappears from the result set entirely.

Understanding the Results

There’s no severity banding here since there’s nothing to grade, just a live status. Read the columns as:

percent_complete is SQL Server’s own progress estimate for the operation, not a guess derived from elapsed time.

running_time is how long the operation has been active so far.

estimated_time_remaining and estimated_completion_time are SQL Server’s projection based on progress so far. Both assume a roughly constant rate; a backup or restore that speeds up or slows down partway through (contention, a network hiccup for a remote target, another job starting) will shift this estimate as it goes, so treat it as a working estimate, not a promise.

An empty result set is not a problem to fix. It means nothing matching the tracked command types is currently running, which is the normal state most of the time.

Best Practices

Run this on demand during an active operation, not as part of a scheduled health check; an empty result most of the time is expected, not a finding
Re-run it periodically during a long operation rather than once, since the estimate can shift as the operation progresses
Use it before deciding whether to wait out a long-running restore or escalate, rather than guessing from elapsed time alone
Remember it only reports what SQL Server itself is actively tracking; an operation that’s hung or blocked rather than progressing may still show a stale percent_complete, so cross-check with blocking and wait information if progress looks frozen

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Why does this script sometimes return no rows?

Because nothing matching the tracked commands is currently running. That’s the normal, expected state most of the time. This script only returns rows while a backup, restore, or DBCC space-reclaim operation is actively in progress.

Is SQL Server’s percent_complete accurate?

It’s SQL Server’s own internal estimate, generally reliable for a steady-state operation. It can shift if the operation’s speed changes partway through, so treat the estimated completion time as a working projection, not a guarantee.

Can I use this to check restore progress from a different session or job?

Yes. It queries server-wide active requests, not just your own session, so it shows any qualifying backup or restore operation running on the instance, regardless of who or what started it.

Summary

The gap between “it’s still running” and “it’ll be done in eight seconds” matters most during exactly the situations where guessing is worst: a live restore, a backup racing a maintenance window, an operation someone else started that you’ve just inherited. This script closes that gap using SQL Server’s own progress tracking.

Keep it on hand for the moments that actually need it. An empty result most of the time is normal; a live percent complete and an honest time estimate is what you want the moment it isn’t.

Database Backups and Recovery Performance Troubleshooting

Blog Categories

Comments

Leave a Reply Cancel reply