Archive for January, 2012

Backup/Restore Time Estimations

January 19, 2012 Leave a comment

Often times I have seen people run through the GUI in SSMS to perform backup or restore operations only to see the spinning wheel and not knowing how much longer or what percentage complete the command is at.  I stumbled across this script a few years ago and use it quite often to determine time left for a command and percentage complete.  It also works for other operations such DBCC CHECKDB.

use master

start_time ,
command, AS DatabaseName,
DATEADD(ms,estimated_completion_time,GETDATE()) AS RemainTime,
(estimated_completion_time/1000/60) AS MinutesToFinish
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b
ON a.database_id = b.database_id
WHERE command like '%restore%'
or command like '%Backup%'
AND estimated_completion_time > 0

Categories: Uncategorized