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

When trying to access the database in SQL Server, you may receive the following error message:

Database xxx cannot be opened. It is in the middle of a restore.

If you check the status of the database in SSMS, it will show as restoring (see the below image).

Alternatively, you can check the database status by using the following T-SQL command.

USE master
go
SELECT DATABASEPROPERTYEX(‘stellar’, ‘status’) status

Here, ‘stellar’ is the database name.

You can also use the sys.databases system view to check the database status.

USE master
go
SELECT state_desc from sys.databases
where name=’stellar’

It will display the following result:

Here, ‘state_desc’ is the description of the database state.

Reasons for the Database cannot be Opened Issue

If the database is in restoring mode, you cannot use the database. This is why it cannot be opened. However, there are also some other reasons that may lead to this issue, such as:

• The restoration process failed unexpectedly and the database remains in that state.
• There is not enough disk space on the server where the database is stored.
• When restoring the database, you set it in No Recovery mode by mistake.

Solutions to Fix the Database cannot be Opened Issue

If the database is in restore mode, it means that someone is restoring the database. You can contact other DBAs and verify who is restoring the database. If this is the case, then you need to wait until the database is restored.

However, sometimes, the restoring process fails. In such a case, you need to fix the problem.

Let’s assume that you already have a database backup.

To set the database in recovery mode, you can try to restore the database.

• In SSMS, go to the Object Explorer, right-click the database, and select Restore Database.

• In the Restore Database window, select the database that you want to restore and then select the Backup sets to restore.

• In Options, select the Recovery State as RESTORE WITH NORECOVERY.

Basically, there are 3 possible states:

1. RESTORE WITH RECOVERY is the default option. It is the last backup. You do not have to backup more backup sets.
2. The NORECOVERY option is used to restore from a backup. Set it to restoring mode and wait for other backups to be restored.
3. STANDBY is another option that keeps the database in read-only mode. It allows you to check the data and verify that you have the correct data during restoration.

Also, you can use the command line to set the database to Restore mode. Use the following code:

USE [master]
BACKUP LOG [stellar] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar_LogBackup_2023-01-19_18-05-01.bak’ WITH NOFORMAT, NOINIT, NAME = N’stellar_LogBackup_2023-01-19_18-05-01′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [stellar] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [stellar] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar_LogBackup_2023-01-19_13-00-38.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [stellar] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar_LogBackup_2023-01-19_16-58-07.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO

• To fix the problem, restore the database using the RECOVERY option.

Alternatively, you can use the following command:

USE [master]
BACKUP LOG [stellar] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar_LogBackup_2023-01-19_18-19-32.bak’ WITH NOFORMAT, NOINIT, NAME = N’stellar_LogBackup_2023-01-19_18-19-32′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [stellar] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [stellar] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar_LogBackup_2023-01-19_13-00-38.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [stellar] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER03\MSSQL\Backup\stellar_LogBackup_2023-01-19_16-58-07.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5
GO

What to do if the above solution does not work?

It may be possible that your database is corrupt. If that is the case, you can use a third-party SQL repair software, such as Stellar Repair for MS SQL to repair the database. To use the software, follow these steps:

Note: Before proceeding, take your database offline.

Download and install Stellar Repair for MS SQL. Then, open the software and find the database file.

Once you find the file, select it and press the Repair button.

After repairing, you can also export the data to different formats, like Excel, CSV, and HTML.

Conclusion

In this article, we discussed the “database cannot be opened. It is in the middle of a restore” issue. We also discussed why this problem occurs and how to restore the database and change the status.

We also mentioned Stellar Repair for MS SQL – an advanced software used to restore damaged SQL databases. This software is compatible with any SQL Server version.