Show estimated time to completion
Would be great to see estimated time to completion on the In Progress tab during a restore
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],
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
The data from the dmvs is helpful but on large databases it is not sufficient. My 4TB database takes between 4 and 8 hours to backup. There should be a number of progress messages as the various stages of backup are completed. For example, I have noticed that the DMV will show 100% complete and the Status and Wait Types change but the job still takes an hour or more to actually complete. I think it is probably waiting for the final disk I/O or something similar.
The option to set a couple of levels of "Verboseness" with messages such as "Calculating Disk Space", "Starting File Group xxx", etc.
It would also be nice to have some performance diagnostics to help identify the processes or resources that are holding up the backup process.
It is a real black-box to me and I would like to be able to peek inside sometimes. :)