http://www.sqlservercentral.com/blogs/sqlchicken/2011/12/30/estimated-completion-time-for-backups-and-restores/

Printed 2014/08/28 05:28PM

Estimated Completion Time for Backups and Restores

2011/12/30

I’m in the middle of a database migration and thought I’d quickly share a script I threw together to show estimated time of completion for a database restore in progress on SQL Server. The script will also show you estimated time for database backups to complete as well.

Please don’t take this script as gospel, the best way to truly know how long restores will take is to actually perform a restore! Remember folks:

Backups are worthless, restores are pricess

SELECT
	session_id,
	start_time,
	status,
	command,
	percent_complete,
	estimated_completion_time,
	estimated_completion_time /60/1000 as estimate_completion_minutes,
	--(select convert(varchar(5),getdate(),8)),
	DATEADD(n,(estimated_completion_time /60/1000),GETDATE()) as estimated_completion_time

FROM    sys.dm_exec_requests where command = 'BACKUP DATABASE' OR command = 'RESTORE DATABASE'

NOTE: Due to the fact this script uses DMV’s, will only work on SQL Server 2005 and higher

Post to Twitter Tweet This Post



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.