In my case I had been restoring one database over another (Production database into a Test database) and one of them got stuck… not funny.
After selecting the MASTER database to run the query against, the command is:
RESTORE DATABASE <database name> WITH RECOVERY
As you can see this fixed it in 4 seconds!
In case this does not resolve your issue, I found http://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state to be helpful.