Script To Get Estimated Database Backup or Restore Time

This post contains a SQL script to get estimated completion times and process information for your currently running database backups & restores in SQL Server.

Backing up a database can often take a very long time. It depends on various factors, one obvious reason being, that the bigger your database gets, the longer it will take to backup & restore.

Other backup performance considerations include whether your server is busy with transactions/queries, or you may be running on old hardware, and I’ve seen having fragmented heaps cause extended backup runtimes too.

If you’re backing up or restoring a large database in SQL Server, you can run the SQL script below to find out how long it has left to complete. The SQL script will return an estimated database restore date/time & percentage completed so far.

SQL Script to get ETA for Database Restore

Copy and run this query to check how long your database backup or restore is going to take:

-- get estimated db restore time sql
-- (tested on sql server 2022 and below)
SELECT 
    command,
    s.text,
    start_time,
    percent_complete,
    CAST(((DATEDIFF(s,start_time,GETDATE()))/3600) AS VARCHAR) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GETDATE())%3600)/60 AS VARCHAR) + 'min, '
        + CAST((DATEDIFF(s,start_time,GETDATE())%60) AS VARCHAR) + ' sec' AS running_time,
    CAST((estimated_completion_time/3600000) AS VARCHAR) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 AS VARCHAR) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 AS VARCHAR) + ' sec' AS est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, GETDATE()) AS est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG', 'DbccSpaceReclaim','DbccFilesCompact')

As shown in the screenshot above, the SQL script returns process info for all BACKUP, RESTORE & DBCC commands running right now. It includes the start datetime, the SQL command, percent completed, and estimated completion time which are all being read from the sys.dm_exec_requests & sys.dm_exec_sql_text system dynamic management views (DMVs).

We can also use sp_whoisactive to get similar SQL process information, it doesn’t include the Est_Completion_Time column however you will see a Percent_Complete column.

Backup & Restore Performance

I’ve seen a multi-terabyte database take over 2 days to complete a full backup. Knowing how long your backups are taking can often be vital for reviewing database backup & restore strategies, and/or you might want to try to improve backup restore performance.

The performance of a backup or restore in SQL Server will depend on many factors. For example:
# SQL Server Performance: Server load, I/O subsystem health, and Temp DB configuration directly affect backup and restore times. Fine-tuning parallelism and wait resource allocation ensures consistent performance under varying workloads. We should consider scheduling backups to run at the least busy time of day for your SQL Server.
# Backup Options: Features like compression, striped backups, and encryption impact performance by balancing speed and CPU usage.
# Hardware: Disk speed, network bandwidth, and CPU/RAM performance are critical factors in backup and restore speeds. Optimized hardware reduces I/O bottlenecks and ensures efficient processing during operations.

Hope this helps. Feel free to add a comment if you have a question or if some of these recommendations worked for you!