Database [DatabaseName] Cannot Be Opened. It Is in the Middle of a Restore is a common error in SQL Server that can happen when you are trying to query, alter or drop a database while it’s still in the middle of a Restore Operation.
When a database is in the RESTORING state, most ALTER DATABASE statements will fail. The exception is setting database mirroring options. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.
Error When Trying to Drop Database in SQL Server
When attempting to Alter or Drop a database, the error message might appear as follows:
-- Alter failed for Database 'DatabaseName'. (Microsoft.SqlServer.Smo) -- Database 'DatabaseName' cannot be opened. It is in the middle of a restore.alt
Resolution: Bring Database Out of ‘RECOVERY’ Mode
Execute the following SQL query to bring the database out of ‘RECOVERY’ (Restoring) mode:
-- Bring database online, out of no recovery USE master GO RESTORE DATABASE [DatabaseName] WITH RECOVERY;
This action should make the database accessible, allowing you to proceed with the intended operation. For me, I want to drop the database, but you might want to start querying the data. Enjoy!
Still Having Issues?
If you are still encountering issues, our next best step is to ensure there are no active connections on the database we’re trying to drop. Running a script to terminate all User SPIDs (Server Process IDs) related to the database can be beneficial. For the script to kill all User SPIDs, refer to our other post: SQL Server Script: Kill All User SPIDs on a Database.
Otherwise, feel free to comment below explaining your issue and we’ll help resolve!
Leave a Reply