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

This Blog will explain how to get time required to complete backup of database command executed by some another user.

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.