Sometimes you want to quickly see if there are any databases or logs being backed up or restored at this moment. I blogged at one point how you can check how much longer the restore will take here: How much longer will the SQL Server database restore take. The other day someone wanted to know this information for all databases on a server, he wanted to know this for restores as well as backups. The query below will give you that info as well as the percentage that is complete for each operation

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
    d.PERCENT_COMPLETE AS [%Complete],
    d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
    d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
    d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
    d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
    d.COMMAND as Command,
    s.text as CommandExecuted
FROM    sys.dm_exec_requests d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
or d.COMMAND     LIKE 'RESTORE LOG%'
OR d.COMMAND     LIKE 'BACKUP DATABASE%'
OR d.COMMAND     LIKE 'BACKUP LOG%'
ORDER   BY 2 desc, 3 DESC
SELECT 
    d.PERCENT_COMPLETE AS [%Complete],
    d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
    d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
    d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
    d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
    d.COMMAND as Command,
	s.text as CommandExecuted
FROM    sys.dm_exec_requests d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
or d.COMMAND	 LIKE 'RESTORE LOG%'
OR d.COMMAND	 LIKE 'BACKUP DATABASE%'
OR d.COMMAND	 LIKE 'BACKUP LOG%'
ORDER   BY 2 desc, 3 DESC

Throw this in a view on your Tools database and you are all set.

This will probably also be added to SQLCop’s informational section