SQL Server – When will my backup finish?

The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup :), but it’s not always possible.

The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.

When a backup is running, you can use the below query to check the progress, 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%'

Result Set:

command              percent_complete     elapsed       remaining

BACKUP DATABASE      50.75982             44.594500     41.207166

For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Gum Naam
    January 23, 2012 at 11:00 am

    It Works Perfect.

  2. Raj
    January 31, 2012 at 1:10 pm

    I dont think to get this information through DMV.Very Good Info for US…..

  3. Raj
    January 31, 2012 at 1:11 pm

    Raj :
    I dont think to get this information through DMV.Very Good Info for US…..

  4. February 14, 2012 at 5:36 pm

    It really helped, thank you for the insightful post.

  5. vamsi
    July 30, 2013 at 8:35 pm

    very very thanks boss its working perfectly……..

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 288 other followers

%d bloggers like this: