[Fixed]: Database cannot be Opened. It is in the Middle of a Restore

When trying to access a SQL database, you may receive an error message, stating that “Database xxx cannot be opened. It is in the middle of a restore.” As indicated from the message, this error occurs if the database is in the ‘restoring’ state. However, there are several other reasons that can lead to such an error message. Below, we will discuss the reasons for this error and see how to resolve it.

Reasons for the Database cannot be Opened Error in SQL Server

Though this error usually occurs when the database is in restoring mode, it can also occur due to the following reasons:

• The backup restoration process failed unexpectedly.
• The backup file you are trying to restore is corrupted.
• There is not enough space on the disk where the database is stored.
• When restoring, the database is set to NO RECOVERY mode.
• When restoring multiple backup files, the “RESTORE DATABASE_WITH RECOVERY” option is not selected.
• Corruption in the SQL database.

Solutions to Fix “Database cannot be Opened: It is in the middle of a restore” Error in SQL Server

When you receive the “Database cannot be opened: It is in the middle of a restore” error, then you can first check the database state. If it is in ‘restoring’ mode, then wait until the database is restored. If this is not the case, then follow below troubleshooting solutions to resolve the error.

Method 1: Restore Database by using WITH RECOVERY Option

When performing the database restore operation if you’ve selected the “NORECOVERY” option, then you may encounter the error “The Database cannot be Opened: It is in the middle of a restore” when accessing the database. This usually happens when restoring multiple backup files. When you select the NORECOVERY option, it keeps the database in the recovery state as it allows to restore more backups from the current location. So, you need to perform the restore operation by using the WITH RECOVERY option to make the database online. Here are the steps to restore the database WITH RECOVERY option:

• Open the SQL Server Management Studio (SSMS). Go to the Object Explorer and right-click the database node.
• Click Tasks > Restore > Database.

• In the Restore Database dialog box, select the Source (full backup you want to restore)and the Destination. Click OK.

• Information about the chosen backup file will be added in the Backup sets to restore section.

• Go to the Options page and select RESTORE WITH NORECOVERY in the Recovery state field. Click OK.

Follow the same process to restore other backups (excluding the final backup file) by using WITH NORECOVERY option. Once you restored all the backups, then you need to restore the final backup file by using the WITH RECOVERY option. For this, perform the above process but on the Options page, select RESTORE WITH RECOVERY and then click OK.

This will bring the database online and make it accessible.

Alternatively, you can also use T-SQL commands (see the below example) to restore multiple SQL databases by using the WITH RECOVERY option.

RESTORE DATABASE [YourDatabaseName]

FROM DISK = N'C:\Path\To\Your\FullBackup.bak'

WITH NORECOVERY,

     STATS = 10;

RESTORE DATABASE [YourDatabaseName]

FROM DISK = N'C:\Path\To\Your\DifferentialBackup.bak'

WITH NORECOVERY,

     STATS = 10;

RESTORE LOG [YourDatabaseName]

FROM DISK = N'C:\Path\To\Your\TransactionLogBackup1.bak'

WITH NORECOVERY,

     STATS = 10;

RESTORE LOG [YourDatabaseName]

FROM DISK = N'C:\Path\To\Your\LastTransactionLogBackup.bak'

WITH RECOVERY,

     STATS = 10;

Method 2: Use a Professional SQL Repair Tool

If the restoration process fails or the database is still not accessible after restore, then there are chances that the backup (.BAK) file is corrupted. There is no native method available to repair the corrupt backup file. The only option is to use a third-party MS SQL database repair tool, such as Stellar Repair for MS SQL Technician. It is an easy-to-use DIY tool that can repair corrupted SQL Server database files and backup files. It can easily restore all the data from the corrupt backup file and save it in a new database with 100% integrity. The tool can help resolve all the errors related to corruption in SQL database and backup files.

Conclusion

The error “Database cannot be opened: It is in the middle of a restore” in SQL Server occurs when the database is in restoring state. You can follow the solutions discussed in this article to resolve the issue. If the error has occurred due to corruption in backup file, then you can use a reliable SQL repair tool, such as Stellar Repair for MS SQL Technician to repair the file. This powerful tool can repair SQL database and .BAK files with complete precision. It supports MS SQL version 2022, 2019, and earlier, and is available for Windows and Linux operating systems.

Was this article helpful?

YES0

NO

About The Author

Monika Dadool

Monika Dadool is a Senior Content Writer at Stellar with over 5 years of experience in technical writing. She is a tech enthusiast and expert who specializes in writing about SQL Server, MySQL Server, MariaDB Server, Microsoft Access, Active Directory, email recovery, Microsoft 365, pattern recognition, machine learning, data recovery, file repair, and operating systems like Linux, Windows, and Mac. She also writes about accounting software such as QuickBooks and Sage 50, as well as web-scripting languages like HTML, JavaScript, Python, PHP, Visual Basic, ASP.NET, and AJAX. Monika is passionate about researching and exploring new technologies, and she enjoys developing engaging technical blogs that help organizations and database administrators resolve various issues. When she's not creating content, you can find her on social media, watching web series, reading books, or exploring new food recipes.