How To Estimate When a Database Backup Will Complete

Issue
How to determine the percent of completion and estimated completion time of an SQL database backup that is in progress.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

Cause
Often when a backup has begun executing either by another party, or started on another computer, or by another method where the GUI is not visible it would be
beneficial to determine when the backup will complete.  This is particularly true when working with larger databases or if it is suspected that the backup
is not progressing.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

While a backup is in progress, you can execute the below query to check the progress.  This will display the 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
FROM sys.dm_exec_requests
WHERE command like 'BACKUP%'

** see attachment containing the SQL script text

Released for: Schneider Electric Canada