To understand how to avoid “Database ‘Adventureworks’ cannot be opened. It is in the middle of a restore” error let’s consider the following example:
Assume, in the database some crucial data were deleted, and the restore operation was started
RESTORE DATABASE Adventureworks FROM DISK = 'Adventureworks_full.bak' WITH NORECOVERY, REPLACE
Note, that even if the full backup was restored the database still is in the NORECOVERY state and there is no way to use it. But if you try to run this database the SQL Server will send this error message:
Msg 927, Level 14, State 2, Line 3 Database 'Adventureworks' cannot be opened. It is in the middle of a restore.
Here are some ways how to avoid this error.
- The first one is to restore only full backup WITH RECOVERY option:
RESTORE DATABASE Adventureworks FROM DISK = 'Adventureworks_full.bak' WITH RECOVERY
- The other way is applicable when you need to restore several backups, such as differential and transaction log. Doing this you need to restore the last backup WITH RECOVERY option in order to restore the database to its working state.
