Estimating backup/restore duration

How many times have you been watching the progress bar during a SQL Server Backup or restore operation? And how times have you answered "Wel, I guess ... " when people asked you "Hey mister DBA, how much more time will it take before the restore has completed?".

I have been doing quite some backup/restore operations during recent upgrading and migration projects. The sys.dm_exec_requests DMV contains one row for every running command on a SQL Server instance. Two interesting columns (estimated_completion_time, percent_complete) help you answering the "when" and "how much longer" questions.

The SELECT statement below will give you a time estimation for every running "BACKUP DATABASE" or "RESTORE DATABASE" statement. Play around with the WHERE clause to monitor other long running operations.

/* Created by free online sql formatter */


SELECT command, 'EstimatedEndTime' = Dateadd(ms,estimated_completion_time,Getdate()),
'EstimatedSecondsToEnd' = estimated_completion_time / 1000,
'EstimatedMinutesToEnd' = estimated_completion_time / 1000 / 60,
'BackupStartTime' = start_time,
'PercentComplete' = percent_complete
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')