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
) - 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
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