Last night, I was migrating a production database from SQL Server 2005 to SQL Server 2008 environment. While the restoration script was running, failover happened due to a network failure. When the SQL instance started on the other node, all the databases are came online; however, the database, where I was doing a restore, went into restoring mode. So I tried to bring the database online by executing the following command:
USE [master];
GO
RESTORE DATABASE [SKG_TEST] WITH RECOVERY;
GO
Unfortunately, I was unable to bring the database Online, so the next thought of asking for help. I had posted my query on twitter, including the #SQLHelp hashtag. Within a few moment, I got two reply, one was to use the above command, the second one to use “WITH RESTART” clause along with the existing restore script. As I never used this clause earlier, so I spend a little time to read more about it. A little search on MSDN, helped me. I got two articles on how to use the WITH RESTART clause, you can read more about it here and here. As the above articles matches with my scenario, I thought of trying the same. I re-execute the restore script with RESTART clause, and the restoration of the databases started immediately.
-- Restoring Database With RESTART option
USE [master];
GO
RESTORE DATABASE [SKG_TEST] FROM DISK = 'E:\Migration\SKG_TEST.bak'
WITH MOVE 'SKG_TEST' TO 'E:\USERDB\DATA\SKG_TEST.MDF',
MOVE 'SKG_TEST_LOG' TO 'E:\USERDB\LOG\SKG_TEST_LOG.LDF',
RESTART, REPLACE;
GO
After a few hours, the restoration was completed. 
After the restoration, the logins were transferred and the migration process was completed. I would like to thank the SQL Community for their quick response, Thanks everyone for your quick reply. It saves me a few hours 
Happy Learning
Thanks for Sharing!
It was a good learning experience
It is a useful tip!