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