Working out how long a SQL Server backup/restore will take
Working out when a database backup or restore operation will take to complete can be a real pain. Fortunately, the sys.dm_exec_requests DMV has some useful columns which can help us predict this. Here’s the query I put together whilst waiting for my backup performance tests to complete:
SELECT r.session_id, CONVERT(NVARCHAR(22),DB_NAME(r.database_id)) AS [database], r.command AS [backup_process], s.last_request_start_time AS [started], DATEADD(mi,r.estimated_completion_time/60000,getdate()) AS [finishing], DATEDIFF(mi, s.last_request_start_time, (dateadd(mi,r.estimated_completion_time/60000,getdate()))) - r.wait_time/60000 AS [mins left], DATEDIFF(mi, s.