XML FeedsFrequently 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
tsql
- 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 |
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
I blogged about this a while ago - next step is to show for multiple backups and restores, going at the same time:-).
Nice, did not know sys.dm_exec_requests has ESTIMATED_COMPLETION_TIME of a backup restore.
Good one.
I 
I didn't know about this before. This is helpful because I'm one of those "nagging types."
Try estimating the exact time of completion since your business users don't have to do the math ;-)
I think you should have sent them to Denis :) 
Change restore database to backup database. See here:
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.