Estimate Backup and Restore Completion Time in SQL Server
SELECT
command AS command,
est.text AS sql_text,
start_time AS start_time,
percent_complete AS percent_complete,
CAST(((DATEDIFF(SECOND, start_time, GETDATE())) / 3600) AS VARCHAR)
+ ' hour(s), '
+ CAST((DATEDIFF(SECOND, start_time, GETDATE()) % 3600) / 60 AS VARCHAR)
+ ' min, '
+ CAST((DATEDIFF(SECOND, 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 estimated_time_remaining,
DATEADD(SECOND, estimated_completion_time / 1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE er.command IN
(
'RESTORE DATABASE',
'BACKUP DATABASE',
'RESTORE LOG',
'BACKUP LOG',
'DbccSpaceReclaim',
'DbccFilesCompact'
);
|
|
|
|
|
|