SQL Script to Review Backup and Restore Processes

A coworker shared this awesome script to monitor backup and restore operation progress. I am not sure of the origin, but is definitely has become an essential member of my SQL scripts folder.

Results returned:

Command = Type of backup or restore operation,
Text = Actual SQL Command running,
start_time = When process began,
percent_complete = Percentage of completion,
running_time = How long the process has been running,
est_time_to_go = Estimated time left,
est_completion_time = Estimated completion time and
current_date_time = System Date

SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ‘ hour(s), ‘
  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + ‘min, ‘
  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ‘ sec’ as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ‘ hour(s), ‘
  + CAST((estimated_completion_time %3600000)/60000 as varchar) + ‘min, ‘
  + CAST((estimated_completion_time %60000)/1000 as varchar) + ‘ sec’ as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time,
GETDATE() as current_date_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in (‘RESTORE DATABASE’, ‘BACKUP DATABASE’, ‘RESTORE LOG’, ‘BACKUP LOG’)

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Tiny URL for this post:
 

Leave a Comment


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

WordPress Anti Spam by WP-SpamShield