SQL-Articles

Estimated Time for Backup / Restore

This script can be used to find the estimated time of backup and restore that is on progress in your SQL server. This script is applicable for SQL server 2005 and above.

Script

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Sample Output

session_id command Percent Complete ETA Completion Time Elapsed Min ETA Min ETA Hours
52 BACKUP DATABASE 95.76 2008-02-08 08:09:48 0.16 0.01 0.00 Backup database AdventureWorks to disk=’c:adw.bak’

Posted

in

by

Sugeshkumar Rajendran

Tags:

Comments

7 responses to “Estimated Time for Backup / Restore”

  1. PseudoSsiah Avatar
    PseudoSsiah

    I’m using SQL 2008 R2 Express and had no issues with this. Worked like a charm. Thanks!

    Reply
  2. How to view ETA for backup and restore | padawandba

    […] 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 […]

    Reply
  3. Dave Ricketts Avatar
    Dave Ricketts

    Worked fast and perfectly. Note: Do not substitute your DB names within the section: IN (‘RESTORE DATABASE’,’BACKUP DATABASE’). These literals are actually the correct values to use.

    Reply
  4. Mohamed M Abdelfatah Avatar
    Mohamed M Abdelfatah

    Thanks Sugeshkumar, great article.

    So good, that I republished it under my own name without giving you any credit. For the images, I did not even copy them, but just linked them back to your article.

    Let me know what you think of whats is now my work: http://sqlunv.com/BI/?p=156

    Mohamed

    Reply
  5. Jerry Avatar
    Jerry

    Really useful piece of code to allow me to create an estimation plan for an upgrade

    Reply
  6. Randy Davis Avatar
    Randy Davis

    Very useful query. I do have an observation, however. When I do a Restore Files and Filegroups (as opposed to Restore Database), there is no ETA or % complete data returned. Does this data not exist for this type of restore?

    Reply
    1. VidhyaSagar Avatar
      VidhyaSagar

      Thanks Randy. Let me take a look at this and update you later.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *