Script to monitor backup and restore progress

I have been recently busy with tuning the restore performance on a testing server. The original disk array was configure to be RAID5 and the restore process was super slow which cost 16 hours to restore a 300 GB database backed up from prod. I've also double-checked CPU and memory stats, none is believed to be the bottleneck. So we decided to downgrade RAID5 to RAID0 since our testing environment doesn't require fault tolerance.

Right now, I am testing the restore again to see if the RAID0 can actually bring the expected performance benefits. However, the restore script I am using was accidentally set to have the argument with "stats = 100" (should've used stats = 1) which will not report progress info until the full restore is finished. So the questions becomes how to monitor a restore process when it is running in the background.

Luckily enough, there are at least two scripts that can be found through Google to monitor the progress of a backup/restore process. The first one is hidden in a thread in SQLServerCentral's forum and the other one is here.

Needless to say, I would like to post them in this post as well.

The first script can only monitor "restore" progress:

SELECT
A.name,
B.total_elapsed_time/60000 AS [Running Time in minutes],
B.estimated_completion_time/60000 AS [Remaining in minutes],
B.percent_complete as [% of completion],
(SELECT TEXT FROM sys.dm_exec_sql_text(B.sql_handle))AS Command
FROM
MASTER.sys.sysdatabases A inner join sys.dm_exec_requests B
on A.dbid = B.database_id
WHERE
B.command LIKE '%RESTORE%'
order by B.percent_complete desc,B.total_elapsed_time/60000 desc

The second script give more general info about both "restore" and "backup".

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')