This site is currently using a generated translation

SQL Server - Estimated backup completion time

Quite often we get questions about how much time is left before a database backup is complete. I usually use the script below to quickly get an idea of how much time is left.

Please note that only the currently running backups are shown and that these are only estimated times, they can be quite different from the real ones.

SELECT r.session_id AS [Session], 
r.command AS [Process],
DB_NAME(database_id) AS [Database],
CAST(r.percent_complete AS decimal(10,2)) AS [Percent complete],
CONVERT(varchar(19),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) + ' (' +
CAST(CONVERT(decimal(12,2),r.estimated_completion_time/1000.0/60.0) AS varchar(10)) +' min left)' AS [Estimated completion time],
CONVERT(decimal(12,2),r.total_elapsed_time/1000.0/60.0) AS [elapsed time (min)]
FROM sys.dm_exec_requests r 

As a bonus, you can also easily use the query to control other processes, e.g. database retrieval, just replace the command type in the where statement.