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’ |
I’m using SQL 2008 R2 Express and had no issues with this. Worked like a charm. Thanks!
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.
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
Really useful piece of code to allow me to create an estimation plan for an upgrade