Monitor Backup and Restore Progress Using DMV's

When using SQL Agent jobs or a third party tool to control database backups on a scheduled basis, you may lose the ability to monitor the progress of the backup job. 

For instance, if we executed the following BACKUP command in a T-SQL window, we have the ability to monitor its progress in the messages window.

BACKUP DATABASE <DBName> TO DISK = N'<File>'
WITH
NOFORMAT,NOINIT,
NAME = N'<Name>',
SKIP,NOREWIND,NOUNLOAD,
STATS = 10
 GO

But when jobs are executed via the SQL Agent, that information is abstracted away from us since it is happening in the background. Luckily, there are a couple Dynamic Management Views (DMV) that can tell us the BACKUP commands progress as well as the exact command issued.

By executing the following query, we can get an idea of how far along our backup, restore or rollback operation has progressed. Unfortunately, this will not work for queries.

 SELECT er.percent_complete
                ,db_name(er.database_id) AS [DB]
                ,st.text
                ,er.command
 FROM sys.dm_exec_requests er
         CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
 WHERE percent_complete <> 0