DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 16--------------------------------------------------
       Estimated Time of Completion of Backup/Restore 
       By: srinivasma_exceldbp Date: March 6, 2014, 1:21 am
       ---------------------------------------------------------
       -- ** Estimated Time of Completion of Backup/Restore
       /*
       If you have started backup/restore process, you would like to
       know,
       percentage of completion and approximate time, the process will
       take to complete
       Estimate completion of long-running backup or restore commands.
       Use the following code to display the Estimated Time  and
       Percentage Completion
       This is general script which can be used for other commands
       like dbcc etc
       */
       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,getda
       te()),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')
       *****************************************************