Fixed: Database Cannot Be Opened as It is in The Middle of a Restore

Aftab Alam Aftab Alam Updated On - 11 Oct 2019

An SQL Server Administrator performs database restoration when some information from the database is missing. The restoration procedure requires the SQL backup file and transaction logs to restore the database data as 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 NORECOVERY mode for the restoration and it does not allow the usage of the database. So, you should now use WITH RECOVERY mode for database restoration as it will make the database accessible to the user.

Instant Solution

Download an automated solution Kernel for SQL Database Recovery for fix Database cannot be opened error in SQL Server.

T-SQL statement for restoring database WITH RECOVERY option

Although the WITH RECOVERY option is activated by default with the full restore procedure, you can mention in the command also.

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

Recover a database from the ‘restoring’ state

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

RESTORE DATABASE DB5343 WITH RECOVERY
GO

Restore multiple backups using WITH RECOVERY

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

RESTORE DATABASE DB5343 FROM DISK = ‘C:\DB5343.BAK’
WITH NORECOVERY
GO
RESTORE LOG DB5343 FROM DISK = ‘C:\DB5343.TRN’
WITH RECOVERY
GO

After completing the restoration process 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:

  1. Start the Studio from the program and go to Databases in the menu list. Right-click the databases and click the Restore database option.
  2. 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.
  3. In the Specify Backup wizard, select the backup file and click the OK button.
  4. Now, in the destination for the restore section select the database where you want to put the recovered data.
  5. In the Restore options, check the option ‘Overwrite the existing database (WITH REPLACE).’
  6. In the Recovery state, select the option ‘Leave the database ready to use by rolling back uncommitted transactions. Additional transactions logs cannot be restored’.
  7. Click OK and let the restore procedure complete.
  8. Perform the same procedure for each backup file which you need to restore 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 which can remove the error from SQL database and restore it in full health. Kernel for SQL Database Recovery is a competent software which can access the database files of SQL server and safely recover it.

Kernel for SQL Database Recovery

The software accesses the SQL database files (MDF/NDF) from its location, automatically retrieves its version, scans it, and repair corrupt SQL Database. Here are multiple features which the tool provides to its user:

  • Repair SQL database from any version of SQL Server.
  • Retrieves all the data of database objects like functions, tables, triggers, rules, deleted records, primary key, foreign key, etc.
  • Handles large sized database files efficiently.
  • Creates a separate backup file of a restored database in script format.
  • Recovers the ASCII and UNICODE characters from the database.
  • Allows the clean preview of data before saving it.
  • Automatically checks the version of SQL Server.
  • Saves the data in the original format and structure.
  • Directly saves the recovered data to a Live SQL Server.