Past week we were using tool for scanning database metadata across all our production servers and at one of them the tool captured following error when trying to access one of the databases:
After some searching and investigating detail about the server we captured two core informations:
- The database is set for read-only access
- The server was upgraded last week to never version (higher service pack).
This combination is the root cause of the database to be inaccessible: because it was set read-only during the instance upgrade, setup wasn’t able to upgrade the database too. The error above appears at every single action to recover now.
All these steps has failed:
1. Set the database for read_write access:
USE master; GO ALTER DATABASE [DB1] SET READ_WRITE; GO
2. Detach the database
3. Backup the database
4. Check if files aren’t locked at the file system level (read-only attribute)
In the end, we have finished with dropping the database and restoring it from the previous backup which led to successful database version upgrade.
It’s really one the best practices that before any patching or upgrade of SQL Server instance all read-only database should be set back to read-write mode.