An SQL Server Administrator executes database restoration when some information from the database is missing. The restoration procedure requires the SQL computer file and transaction logs to revive the database data because it was present before deletion. In some cases, when the restoration is complete, and you try to access the data, then you may get the following error message:

Database ‘DB5343’ cannot be opened. It is in the middle of a restore.

The error occurs because the administrator used the NO RECOVERY mode for the restoration and it doesn’t allow the usage of the database. So, you ought to now use WITH RECOVERY mode for database restoration because it will make the database accessible to the user.

Table of Contents

Toggle

T-SQL statement for restoring database WITH RECOVERY option

Although the WITH RECOVERY option is activated by default with the complete restore procedure, you’ll mention within the command also.

RESTORE DATABASE DB5343 FROM DISK = ‘C:\DB5343.BAK’

WITH RECOVERY

Recover a database from the ‘restoring’ state

If the database is in the ‘restoring’ state and is not available to users, you should run the command to make it accessible to users.

RESTORE DATABASE DB5343 WITH RECOVERY

Restore multiple backups using WITH RECOVERY

SQL Server restoration using the NORECOVERY option can restore the database from numerous back-ups, except in the last step. Using WITH RECOVERY within the last action restores all transaction logs to bring the database online.

RESTORE DATABASE DB5343 FROM DISK = ‘C:\DB5343.BAK’

WITH NORECOVERY

RESTORE LOG DB5343 FROM DISK = ‘C:\DB5343.TRN’

WITH RECOVERY

When the restoration process is completed, then by using WITH RECOVERY command, the database should be online and accessible.

Restore using SQL Server Management Studio

You can perform the task using the WITH RECOVER option using SQL Server Management Studio. Here is the process:

Start the Studio from the program and go to Databases on the menu list. Right-click the databases and click the Restore database option.
Select the option ‘From Device’ and then click the Browse button the go to the location of the backup file which you had recently created.
In the Specify Backup wizard, select the backup file and click the OK button.
Now, in the destination for the restore section select the database where you want to put the recovered data.
In the Restore options, check the choice ‘Overwrite the prevailing database (WITH REPLACE).
In the Recovery state, select the option ‘Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored’.
Click OK and let the restore procedure complete.
Perform an equivalent procedure for every computer file which you would like to revive for the database.

Check if the database is live and accessible to users

After using the two methods, you can restore the database in a healthy state, but if any of these methods do not work, then you need to use a professional SQL Recovery tool that can remove the error from the SQL database and restore it in full health. This tool is a standalone software that can access the database files of SQL server and safely recover it.

Summary

This blog is about how to solve “Database Cannot Be Opened as It is in the Middle of a Restore” error for Microsoft SQL Server.

To resolve the error message “Database Cannot Be Opened as It is in the Middle of a Restore” in Microsoft SQL Server, you can try the following steps. First, check if any ongoing restore operations are running on the database and wait for them to complete. If there are no active restores, you can try restarting the SQL Server service. Additionally, ensure that the database is not set to the “RESTORING” state by running the “RESTORE DATABASE” command with the “WITH RECOVERY” option. If the issue persists, you may need to restore the database from a backup or contact Microsoft support for further assistance.

I hope you find this blog helpful to solve your problem.