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

The performance of a backup or restore in MSSQL will depend on many things. For example, your server hardware, backup options, or user/client queries running in the background.

The estimated time returned by the query below is based on the performance at the time of execution.

-- get estimated db restore time sql
-- works in sql server 2022
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 date-time, SQL command, percent_complete, and estimated completion time.

I’ve seen a multi-terabyte database take 1.5 days to complete a full backup. This information can often be vital for reviewing database backup & restore strategies, or you may want to try to improve backup restore performance.

We can also use sp_whoisactive to get similar SQL process information (excluding est_completion_time) by viewing the percent_complete column in the sys.dm_exec_requests; SQL Server system DMV.