How to view ETA for backup and restore

Once upon a time, I was trying to figure out how to check on the progress of my backups/restores.  While I was investigating, I came across the script in this article (Estimated Time for Backup / Restore).  While very helpful, I’m not a fan of having to do the math for converting the decimal portion to how many actual minutes that is, so I modified it a bit.  I’ve found it quite tidy, so I am going to include it so you don’t have to modify what you find like I had to:

   1: select 

   2:     ER.session_id,

   3:     ER.command,

   4:     convert( numeric( 6, 2 ), ER.percent_complete ) as Percent_Complete,

   5:     convert( varchar( 20 ), dateadd( ms, ER.estimated_completion_time, GetDate() ), 20 ) as ETA_Completion_Time,

   6:     convert( numeric( 10, 2 ), ER.total_elapsed_time / 1000.0 / 60.0 ) as Elapsed_Min,

   7:     convert( numeric( 10, 2 ), ER.estimated_completion_time / 1000.0 / 60.0 ) as ETA_Min,

   8:     (convert( nvarchar( 2 ), convert( numeric( 10, 0 ), round( (ER.estimated_completion_time / 1000.0 / 60.0 / 60.0 ), 0, 1 ) ) ) + N'hr ' + convert( nvarchar( 2 ), convert( numeric( 10, 0 ), ER.estimated_completion_time / 1000.0 / 60.0 % 60.0 ) ) + N'min') as ETA_Hours,

   9:     SQL.text as SQL_Text

  10: from 

  11:     sys.dm_exec_requests as ER

  12:         cross apply sys.dm_exec_sql_text( ER.sql_handle ) as SQL

  13: where

  14:     ER.command in ('RESTORE DATABASE','BACKUP DATABASE');

Advertisements
  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

%d bloggers like this: