Categories
Scripts

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’

Leave a Reply

Leave a Reply

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

*