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.last_request_start_time, (dateadd(mi,r.estimated_completion_time/60000,getdate()))) AS [total wait mins (est)], CONVERT(VARCHAR(5),CAST((r.percent_complete) AS DECIMAL (4,1))) AS [% complete], GETDATE() AS [current time] FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s ON r.[session_id]=s.[session_id] WHERE r.command IN (‘BACKUP DATABASE’,‘BACKUP LOG’,‘RESTORE DATABASE’,‘RESTORE VERIFYON’,‘RESTORE HEADERON’, ‘RESTORE HEADERONLY’)

This should return something like the following:

Figure 1: SQL Server backup finish time prediction

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 not enabled, the first few minutes of a first ever restore will be spent initialising the database files, so the predicted end time will be wildly optimistic. Once the file initialisation is complete the predicted end time will return more accurate information on subsequent restores over the same database – just remember that the transaction log is always initialised (regardless of the instant file initialisation setting) so if the database you’re restoring has a transaction log sized really high this will also result in an optimistic predicted restore time.

Also, for backups utilising the verify option the database column will no longer return the name of the database being backed up, but instead will show the name of the database the backup script is running from once the backup itself has completed and the verify is running.

I have also found that during a large restore the end time is not accurate for the first 5 minutes or so.