Summary: Sometimes, when you attach an .mdf and a .ldf file to a SQL Instance, the process is successful and the database goes online but you found that it is in read-only mode. In this post, we will see why this happens and how to fix this issue.
You have physical mdf and ldf files and carried out the attach operation to put a new database online. The operation does not return any errors. But when you try to write something into the database, a message appears saying the database is read-only.
Why the database is not in the read-write mode?
Well, we can try to understand more about this problem and try to bring the database into the normal read-write mode.
You can do this using the following ?Alter Database? T-SQL command. Execute this command in the SQL Server Management Studio (SSMS) and make sure you logged in using Windows Authentication.
This command would send an error that could give more information.
If you got the following SQL error 5120, it means that the server is unable to open a database file.
If you are getting an access denied message (OS Error =5) from the operative system (OS), follow these steps:
Step 1
Search and open the SQL Server Configuration Manager
The Configuration Manager will show the various instances of SQL server installed.
We have installed two of these. The one above is for SQL 2023 CTP 2.0 while the other is for SQL Server 2019.
With the SQL Server 2023 CTP 2.0 instance, the user used for the Log On is NTService\MSSQL$MSSQLSERVER01.
With the SQL Server 2019 instance, the user used is NTService\MSSQLSERVER.
You must verify that the SQL Server user (in the above example, it is NTService\MSSQL$MSSQLSERVER01) has all the permissions and full access to the folder where the mdf and ldf files are located.
From the SQL Server Management Studio (SSMS), open the security\logins section of the database and locate the right user.
Right-click on the user and choose the Properties item from the menu
Go to the securable tab & Add the user to the list if it does not exist
Step 2
Verify the permission to files and folder.
Locate the folder where the mdf and ldf files are placed by right-clicking on the database name.
Choose properties and then click on the Files tab.
From the File System, go to the folder (?c:\luca? in the below example) and right-click on the Properties option.
Select the Security tab.
Click on Edit.
Verify that Administrator has full control.
Now, go to the mdf file.
Right-click on the mdf file and click on the Properties option.
Click on the Security tab and then press the Advanced button.
Click on the Permissions tab and then press the Continue button.
Press the ADD button and add the right user. Remember to give full control to this new user. In the below example, we have added the user – MSSQL$MSSQLSEVER01
Repeat the same procedure for each MDF file you have in the folder.
Note: If you have problems with your mdf and ldf files, you can take the help of advanced SQL repair software, such as Stellar Repair for MS SQL. This is an easy-to-use software designed to repair damaged or corrupt MS SQL Server database files. This software allows saving your data in a new or existing databases and various other formats, such as text, CSV, and Excel. It supports all the versions of SQL Server, including the latest SQL Server 2019.
Things to Remember
An important concept to remember is that when a read-only database is detached and then reattached, the backup information about the current ?differential base? is lost. The ?differential base? is simply the most recent full backup of all the data in the database.
Without this information, the master database becomes unsynchronized with the read-only database and differential backups taken thereafter may provide unexpected results.
So, if you are using differential backups with a read-only database, you should remember to establish a new differential base by taking a full backup after you reattach the database.
Furthermore, attaching a database places it in the same state that it was in when it was detached or copied.
Finally, attach and detach operations disable cross-database ownership chaining for the database. You should enable chaining manually.
To Conclude
In this post, we have talked about a problem that could arise during the attach phase of a database. The database is attached and goes online but it remains in read-only mode. You can follow the solutions mentioned in this post to resolve the issue. In case you face issues with your database or the database gets corrupted, you can use Stellar Repair for MS SQL to repair and recover the database.
