SQL Script to find Expected Finish TIME for current running BACKUP / RESTORE

Leave a comment (0) Go to comments

When SQL Server backup / Restore will be finished ?

How long it will take to complete this backup or Restore ?

What is expected time when SQL Server BACKUP / RESTORE will be finished?

This question is asked by lot of time by DBA’s and we always answer them either based on past history or looking at current backup status by calculating % and elapsed time.

But there is better way to do that, the following Script help you to determine, more precise information that when the actual backup / restore will be finished.

SELECT
    session_id,
    start_time,
    status,
    command,
    percent_complete,
    estimated_completion_time,
    estimated_completion_time /60/1000 as estimate_completion_minutes,
    DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time, getdate() as'Current Time'
FROM    sys.dm_exec_requests where command = 'BACKUP DATABASE' OR command = 'RESTORE DATABASE'

The following Script will have the following OUTPUT columns as RESULT


  • Session_id – Session ID, which initiated the BACKUP / RESTORE Operation
  • START_TIME- When the backup was actually started
  • Status – Current Status of process
  • command –What sort of SQL Operation is performing (BACKUP / RESTORE)
  • percent_complete  - % completed as of now
  • estimated_completion_time  - What is expected time to complete this backup operation
  • estimate_completion_minutes – pending expected number of minute to complete the backup (approx. break time SQL Script to find Expected Finish TIME for current running BACKUP / RESTORE sql server backup sql backup status script restore backup  )
  • estimated_completion_time  -Final expected data and time, when backup will be finished, this is nothing just a submission of Start TIME + estimate_completion_minutes
  • Current Time – What is current System Date and time, help you, if you are in different time zones.

OUTPUT Screenshot

SQL Script to find Expected Finish TIME for current running BACKUP / RESTORE sql server backup sql backup status script restore backup

TAGS : backup status script,backup exec 12.5 job status queued,time machine backup status,check sql backup status,backup status and configuration,sql server backup status,backup exec status server paused,sql backup status,backup exec job status ,mysql complete backup,backup exec suspended status,,,sql backup complete,backup status,sql backup status,estimated backup completion time,backup completion time,sql backup completion time,backup complete time, rman restore status script,restore exec 12.5 job status queued,time machine restore status,check sql restore status,restore status and configuration,sql server restore status,restore exec status server paused,sql restore status,restore exec job status ,mysql complete restore,restore exec suspended status,,,sql restore complete,restore status,sql restore status,estimated restore completion time,restore completion time,sql restore completion time,restore complete time

EOF - SQL Script to find Expected Finish TIME for current running BACKUP / RESTORE, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.