You need to use the
WITH RECOVERY option, this seems likely caused by the restore script adding the
WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. With your database RESTORE command, to bring your database online as part of the restore process.
This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.
Your command should look like this,
**RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE, RECOVERY, STATS = 10**
You may have more success using the
restore database wizard in SQL Server Management Studio (SSMS). This way you can select the specific file locations, the overwrite option, and the
WITH Recovery option. Sometimes, The restoration process stuck just because of size of the database file.
You can either Use Below one:
How to Change the state from RESTORING To ONLINE
The following will go through each of the lines returned by the query building a
RESTORE DATABASE statement around the name, using
WITH RECOVERY at the end. The statement is then shown in the messages window which you can then use as needed. We'll make use of the state value to restrict the statement to only altering those that are not at state 1.
DECLARE @SetWithRecovery nvarchar(max) = ''
SELECT @SetWithRecovery += 'RESTORE DATABASE ' + quotename(Name) + ' WITH RECOVERY; ' + Char(10)
WHERE [State] = 1
--If you wish to execute it as well
When you execute the code you'll receive a message for each restore statement showing the processing information.
Before you do this, please make sure you understand the options what you has to do, if not it may cause data loss.
Check this link if needed: