Just stumbled into this: if you have a large DB to be restored, it might take forever to actually see the progress moving to 10%, 20%, and so on. Yeah, I know, you should never do a restore from the SSMS, but don’t try to tell me thatJ.
Anyway, coming back to our scenario: if you have started the restore process, it might take quite a while to see that our particular restore is progressing.
So here’s a quick look at it:
SELECT GETDATE() as runtime, command, total_elapsed_time, percent_complete, estimated_completion_time, wait_time, wait_type FROM sys.dm_exec_requests
You’ll get a whole bunch of stuff, but we’re going to focus on the “command” part of it, because this is of interest for us:
As you can see, there’s a RESTORE DATABASE at the second column (command, in our script).
If I want to focus only on that particular command, I simply add a where clause to the above script:
WHERE command = 'RESTORE DATABASE'
Of course, in the percent_complete column I get to see something above 0, if everything is ok.
I hope this helps.
Edit: full article here – as usual, PSSQL team rocks.