SQL Server- Get Time Required to Complete Backup Of Database.

Problem:-

Many Times we need to take Backup of Database. When we start Backup we can see how much time remaining to complete the backup.

But suppose someone else is taking backup of database through GUI or Query, we can't get to know the time remaining to complete backup.

So, Solution for this is table sys.dm_exec_requests

Solution:-

We can find the percent of backup completed and estimated completion time using this sys.dm_exec_requests.

When a backup is running by another person, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:

Query:

SELECT command, percent_complete,

'elapsed time' = total_elapsed_time / 60000.0,

'remaining time' = estimated_completion_time / 60000.0

FROM sys.dm_exec_requests

WHERE command like 'BACKUP%'

Sample Output:

command percent_complete elapsed remaining

BACKUP DATABASE 50.75982 44.594500 41.207166

Thank You.