Frequently you will be asked how much longer a restore will take because someone needs to do something with that specific database that is restoring right now
Of course we all know that the RESTORE DATABASE command has the STATS n option, this will give you the percentage completed. This is nice but it doesn’t tell you when it will complete and if someone else started the restore how will you know how long it will take in that case?
Fear not, here is a query that will tell you exactly how long
T-SQL | |
1 2 3 4 5 6 7 8 9 10 11 | 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 |
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
Here is the output for a fairly large database restore that I started last night
%Complete | ElapsedTimeMin | TimeRemainingMin | ElapsedTimeHours | TimeRemainingHours | Command |
---|---|---|---|---|---|
78.6186 | 766 | 208 | 11.03301576 | 2.99693760 | RESTORE database SomeDB |
14 Comments
I blogged about this a while ago – next step is to show for multiple backups and restores, going at the same time:-).
Also, because your exposure is so much larger than mine, can you tell people about “Perform Volume Maintenance Tasks”? and how the SQL Server should be assigned to that policy? Otherwise it will take forever for the backup to complete.
i’m not sure what i said, but it’s the restore that will take much less:-)
Nice, did not know sys.dm_exec_requests has ESTIMATED_COMPLETION_TIME of a backup restore.
My suggestion to Microsoft is to include this calculation into the status message showing the percentage complete.
I guess it better be rounded to two digits or even hours/minutes.
Thank you Denis!
Good one.
Didn’t know this before.
I
like
your
blog
Denis
I didn’t know about this before. This is helpful because I’m one of those “nagging types.”
I like to know when a restore completes so that I can wrap my schedule around it. It’s just pure agony not knowing.
Try estimating the exact time of completion since your business users don’t have to do the math 😉
http://timlaqua.com/2009/09/determining-when-restore-database-command-will-complete-sql-server-2008/
I just tried this out and it works! Now I don’t have to nag while waiting.
I gave this to a friend and she thanked me with a box of chocolates. Thank you very much, SQLDenis.
I think you should have sent them to Denis 🙂
BTW, is there a way to estimate how long the backup will take?
Change restore database to backup database. See here: How long will the database backup take?
Thanks for info, but i have a question. are there any conditions wherein the above query return 0 for Timeremaininghours.
Math for calculating hours is not correct. I used:
— Shows Time Restore Has Run and Estimate of Time Remaining. —
SELECT
d.PERCENT_COMPLETE AS [%Complete],
STR((d.TOTAL_ELAPSED_TIME / 60000.0000),10,4) AS ElapsedTimeMin,
STR((d.ESTIMATED_COMPLETION_TIME / 60000.0000),10,4) AS TimeRemainingMin,
STR(((d.TOTAL_ELAPSED_TIME / 60000.0000) / 60.0000),10,4) AS ElapsedTimeHours,
STR(((d.ESTIMATED_COMPLETION_TIME / 60000.0000) / 60.0000),10,4) 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
I have a old database backup from 2009 nothing else. I have managed to restore it and now it’s in restoring mode. If I try to “RESTORE” database with recovery, I am getting this error “The database cannot be recovered because the log was not restored”. While doing restore from that old (2009) backup, I was getting specified cast not valid error. However, I managed to restore it with Free SQL Backup Recovery Tool and it works like a charm.