How long will this backup take?

It's a Friday afternoon. You just did an oops and need to fix it. You think you need a backup. You know you need a backup, before you do anything. You quickly kick off a backup job and let it start performing the backup. Now, you know that this job has executed in the past, daily, probably. You can check the history of the job and get an idea of how long it will take.

The last execution was 1:10. A previous execution completed in :45. So you can safely guess that you are in for at least an hour of wait time before completion. This displeases you as you realize that it is already 3:30pm, on a Friday. The backup is already going, so it's a waiting game at this point. Wait, wait, wait.

Your boss suggested that you take off and get some away time, knowing that once the backup completes that there will be a hectic time performing the needed tasks to fix the oops. You consider leaving, after all, the backup will be running for an hour or so... Maybe sooner, maybe longer.

Since this isn't a backup from the UI, you do not have the handy dandy percentage showing up in the corner of the screen, giving you an idea for how long it may take. All you have is a small window saying that the job started, and the spiny guy showing you it's still going. And will complete. At some point.

So, how long will it really take? Only a time machine will truly tell you. But we can make logical guesses. A quick few clicks in Google/Bing would show you several scripts that could help. Here is the one that I found.

SELECT

CONVERT(NVARCHAR(60),DB_NAME(database_id)) AS [database],
CASE command
WHEN 'BACKUP DATABASE' THEN 'DB'
WHEN 'RESTORE DATABASE' THEN 'RESTORE'
ELSE 'LOG BACKUP'
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')


It simply queries the dm_exec_requests and looks for items that indicate a backup. The other fields will massage the resultant data into something that you can use to see how long it may take.

Once I executed this, I was sad, because it showed me over 2 hours to complete. The sad was the duration was longer than I had hoped. The good was that I now had a goal. A time when other tasks would start, after the backup completed. This allowed me a better chance to plan and prepare for those tasks.