Backup | Restore estimated time

Categories:

SQL Server

Display estimated time for backup and restore operations.

--- Backup Estimated Time
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT @@servername as 'Server Name',
command as 'Command',
s.text as 'Statement',
start_time as 'Start Time',
percent_complete as 'Percent Completed',
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour, '
+ 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, '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as 'Estimated Time',
dateadd(second,estimated_completion_time/1000, getdate()) as 'Estimated Completion 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')

Tags:

No Tag

Comments are closed