Monitoring Database Backup and Restore Progress

There are times we want to view the progress of MS SQL Server database backup running in the background, triggered by a maintenance plan (SQL Agent job). Here we can see the percent complete and estimated completion time. This script will work for any backup or restore that is currently running regardless of what method was used to run the backup or restore.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

Reference:
How to monitor backup and restore progress in SQL Server 2005 and 2008

Advertisements

One thought on “Monitoring Database Backup and Restore Progress

  1. I’m excited to uncover this web site. I wanted to thank you for your time for this wonderful read!! I definitely appreciated every little bit of it and I have you saved as a favorite to check out new things on your website.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s