SQL Script to find out how long for backup or restore of database

SQL Server 2008 Backup/Restore progress indicator

One of the usability issues I faced when using the SQL Server 2008 GUI is that when doing a backup or restore, there is no indication of how much longer the backup or restore process will take. This can be especially problematic for large databases, where the percentage indicator moves very slowly. To find out how much longer a backup or restore process will take, execute this SQL code.

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

* Note – I do not take responsibility for any adverse effects that may happen from executing this code. Do so at your own risk.