SQL Server 2005/2008: Find backup and restore status/progress
Ever wondered how long it would take to complete a backup or restore that is running in the background. I, for one, do not want to keep looking at the job or TSQL that is taking the backup or doing the restore.
I found this neat TSQL that will give you the progress report of the backup/restore and will also tell you how long it would take to complete it. In case you are running the backup with the VERIFY option then it does not tell you how long it would take to complete the VERIFY phase.
Run the following TSQL query to get the status
SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')