Working out how long a SQL Server backup/restore will take

Whilst messing around with SQL Server backup performance testing today I remembered a little-mentioned column in the sys.dm_exec_requests output called percent_complete. This is a rather useful column which reports the progress of certain operations and was introduced in SQL Server 2005.

Combined with the output of another column in this DMV called estimated_completion_time we can create a query which neatly displays when a backup or restore operation started, how long SQL Server expects it to take and therefore when it will complete, with a percentage completion column thrown in for good measure.

Here’s the query I put together whilst waiting for my backup performance tests to complete:


select
session_id,
convert(nvarchar(22),db_name(database_id)) as [database],
case command
when 'BACKUP DATABASE' then 'DB'
when 'RESTORE DATABASE' then 'DB RESTORE'
when 'RESTORE VERIFYON' then 'VERIFYING'
when 'RESTORE HEADERON' then 'VERIFYING HEADER'
else 'LOG' end as [type],
start_time as [started],
dateadd(mi,estimated_completion_time/60000,getdate()) as [finishing],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) - wait_time/60000 as [mins left],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) as [total wait mins (est)],
convert(varchar(5),cast((percent_complete) as decimal (4,1))) as [% complete],
getdate() as [current time]
from sys.dm_exec_requests
where command in ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE VERIFYON','RESTORE HEADERON')


This will return the following:

The database name has been converted to varchar so the output of the query looks ok when result output is set to text mode, otherwise the database name gets padded with blank characters up to the width of the sysname datatype.
It comes in rather useful when running a backup or restore operation where the WITH STATS option has not been used – I’m actually finding that this script is preferrable to the output the WITH STATS option provides, as it can be run on demand and the estimate completion time will get updated with the latest predicted time whenever it is run.
Just bear in mind that if instant file initialisation is off the first few minutes of a new restore will be spent initialising the database files so the predicted end time will be wildly pessimistic. Once the file initialisation is complete the predicted end time will return more accurate information.
I have also found that during a large restore the end time is not accurate for the first 5 minutes or so.