The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup , but it’s not always possible.
The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.
When a backup is running, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:
SELECT command, percent_complete,
'elapsed' = total_elapsed_time / 60000.0,
'remaining' = estimated_completion_time / 60000.0
WHERE command like 'BACKUP%'
command percent_complete elapsed remaining
BACKUP DATABASE 50.75982 44.594500 41.207166
For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.
Hope This Helps!
Filed under: Backup & Recovery, Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2