Estimate SQL db Restore Time

If you’ve ever had to restore a large SharePoint or other SQL DB, here’s a handy little T-SQL for guesstimating how long a it’s going to take. Run this when one or more Restore’s are in progress. Note that it would spin up actual TimeRemainingMin’s until it actually picks up speed and get’s going (past 0% in the dialog GUI):

SELECT
    d.PERCENT_COMPLETE AS [%Complete],
    d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
    d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
    d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
    d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
   s.text AS Command
FROM    sys.dm_exec_requests d
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
ORDER   BY 2 desc, 3 DESC

11-22-2013 11-03-25 AM

Keywords:
Method to estimate the recovery time of a SQL Server database
estimated time for restoring a database
Estimated Completion Time for Restores
SQL Server – Restore Database – Time Estimate or Progress
How can I query how much time a SQL server database restore takes?
Working out how long a SQL Server backup/restore will take

backup, Microsoft SQL Server, restore, SQL

Leave a Reply

Your email address will not be published. Required fields are marked *