How to query the progress status of a database restore

Hi,

we have a reporting database that needs to be refreshed every week. I normally get a big backup file (50GB) from our business partner very Sunday morning; I have setup a job to restore the database once I got the backup file. The restore job normally will take about 4 or 5 hours to finish, but sometime it may take much longer (more than 10 hours). We knew there is some kind of disk storage contention going on the SAN. When this problem happens the system guy always try to pick my db restore process to be the victim. I hate to kill a job when it has 90% done, but I have no way to tell the restore progress status. Does someone know if I can query database restore progress information from any DMVs?

Thank you for your help in advance.

David Zhang

Hi D. Zhang,

In your Restore Job specify the stats option and use dbcc outputbuffer(spid).

your restore statment can be like this.

Code Snippet

restore database database_name form disk='c:\backupfile.bak' with stats=1

Stats=1 will show very 1% of the progress.

And then use dbcc outputbuffer() if you need check the progress of restore:

Code Snippet

dbcc outputbuffer(spid)

Hope that helps

Jag

|||

In SQL 2005 you can use the new sys.dm_exec_requests DMV to get estimates for some long running operations like this.

SELECT percent_complete, estimated_completion_time, *
FROM sys.dm_exec_requests

|||

Thank you so much Peter and Jag, both ways provide me the information that I need. Thanks again!

David Zhang