Rotating Header Image

Waiting For A Pot To Boil… Restore Database Progress

BoilingPot

Ever kicked off a restore and didn’t set the STATS parameter?

Ever had a separate application handle a restore and not give you any feedback at all on how far along a restore is?

Well if your answer is no than this post / script is NOT for you  :)

BUT

If your answer is YES then this script should tickle your fancy:

SELECT	A.start_time as 'RestoreStartTime',
		A.percent_complete,
		DATEDIFF(minute, A.start_time, getdate()) as 'CurrentRestoreDurationInMinutes',
		((DATEDIFF(minute, A.start_time, getdate()) * 100) / A.percent_complete) as 'EstimatedTotalDurationInMinutes',
		ROUND(((DATEDIFF(minute, A.start_time, getdate()) * 100) / A.percent_complete) - (DATEDIFF(minute, A.start_time, getdate())), 2) as 'EstimatedTimeRemainingInMinutes',
		B.[text]
 FROM	sys.dm_exec_requests A CROSS APPLY 
		sys.dm_exec_sql_text(A.sql_handle) B
 WHERE	A.percent_complete != 0

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

2 Comments

  1. Chris Wood says:

    Certainly helps us with many scripted big database restores to get an estimate of how much longer they will take.

    Thanks

    Chris

  2. Chris Wood says:

    Seems to work well on a backup process too!

    Chris

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.