SQL Server track Backup Database progress

Occasionally you will kick off a backup in the middle of the day and you want to track the progress so you know when it will finish.

You can get an approximate finishing time by looking in the sys.dm_exec_requests system view.

To read more about sys.dm_exec_requests , please click below: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver15

To show you how, here I kick off a backup of one of my databases.

Now I run this query:

SELECT percent_complete, start_time, status, command, estimated_completion_time,
cpu_time, total_elapsed_time
FROM sys.dm_exec_requests where command='BACKUP DATABASE'

It shows percent complete, estimated amount of milliseconds for completion time and elapsed time. To get the amount of approximate seconds, divide the number by a 1000.

Here is another query that gives you the minutes:

SELECT percent_complete, start_time, status, command,
estimated_completion_time/1000/60 As 'Minutes to Completion',
total_elapsed_time/1000/60 As 'Minutes Elapsed', wait_type, last_wait_type
FROM sys.dm_exec_requests
Where command = 'BACKUP DATABASE'

The above screenshot shows that the backup has been running about 1 minute, and should finish in 3 additional minutes.

These numbers could fluctuate depending on the load currently on the server, but they do provide a good estimate.

After the backup is complete, we can query the database and verify that the backup took 4.5 minutes which our queries above showed

You can learn a lot about your database server by looking at the system views.