How can I tell if my restore process is actually doing something

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.

Best regards,
io

Edit: full article here – as usual, PSSQL team rocks.