I recently received the following comments on my earlier blog about Making database to read only.
“Today I was trying to attach the (MDF, NDF, LDF) SQL server 2008 database which i have received from my client. After attachment the database status is showing (Read-Only) (Eg.database name (Read-Only). How do I make to normal mode for the data updating. Is there any query available to resolve this problem. Your help will be highly helpful.”
Let’s learn Making Database to Read Only and Changing Database to Read/Write with the help of T-SQL scripts.
Here is the answer:
Make Database Read Only
USE [master] GO ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT GO
Make Database Read/Write
USE [master] GO ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT GO
If you face an error that if the database is already in use, you can resolve the same by making database in single user mode – here is the guideline SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
Let me know if you have ever faced this situation in your business or life by leaving note in the comment section in the blog. I read every single comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
48 Comments. Leave new
Excelent Post.Keep it up Man
It is good to know that how can we read/write database. But i have a query that how can i set a password on particular database in windows authentication mode.
Good one
IT IS REAL WORKING , VERY GOOD
THANK YOU MR.
Hi Dave,
I have set the Database for Replication, But the Database went to Read Only, I want to drop that database, and add a fresh copy and want to set that copy for Transaction peer to peer replication. How should I do.
I tried sp_dboption also, but it never worked to me..
Your suggestion on this topic is highly appreciated.
Thanks
Momen
Use this one : restore database [dbname] with recovery
and used sp_removedbreplication ‘Dbname’ to remove replication .
What the purpose to turn mdf to Read-Only? I was assuming that turning database to Read-Only wood do the same to the mdf and I’d not be able to write to mdf, just to run select only. However, it was not true. when mdf is Read-Only I ‘m able to update tables and such.Any clue?
I am getting error Database state cannot be changed while other users are using the database, ALTER DATABASE statement failed.
You need to make sure that the database is not accessed by other users
I have tried same to make my database read\write from read only mode. getting error that
”Unable to open the physical file “E:\AdventureWorks2008_Data.mdf”
“Unable to open the physical file “E:\AdventureWorks2008_Log.ldf”.
Please suggest what to do. ??
I was also getting the same error,please advise what to do in this condition..
Thanks
Great, thanks for script…
If you face error that if database is already in use, you can resolve this by using the “WITH ROLLBACK IMMEDIATE;”
ALTER DATABASE [TESTDB] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
This will drop all connections and set the database to read_only
thanks all for the great info.
should peer to peer transactional replication ( with updates from peers enabled ) withstand network disconnects easily ?
we are using it in an application, set up sqlprime as the primary and sqlsecond as the failover server
all goes well until we test the redundancy by disconnecting the network on sqlprime .
the app find the failover, writes data but when sqlprime ( primary server) comes back, all replication is stopped with strange error ( mainly directing to login failures ( which are bogus as passwords did not change. )
so , are we testing something this setup should not stand ? ( network interruptions ? )
thanks
dave.
Today i was trying to attach the (MDF,NDF,LDF ) sql server 2005 database which i have received from my client. After attachment the database status is showing (Read-Only) (Eg.database name (Read-Only).
i performe all the query ..but still the problem occurs
Hi Pinal Dave,
what is Query that i need fire database into stand by mode a database. Like if we configure Logshipping. Some reason primary server down. And again it is up re-configure the logshipping. On Secondary server we have to fire a query to make it stand by mode… please provide me query.
can you put database in to standby mode? by Queries…
how can we set database in standby mode?
very good
I have a problem the services could not start
Thaknks for your help
Hi,
I have installed SQL2005 – SQL Server Management Studio Express and tried attaching the database.That attach was successful, but the issue was next to database name it was saying read-only.
After that i tried these commands
USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT
GO
But i got this error ,
———————————————————————————————————————
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\Soft\Data\Data_Data.MDF”. Operating system error 5: “5(Access is denied.)”.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\soft\Data\Data_Log.LDF”. Operating system error 5: “5(Access is denied.)”.
File activation failure. The physical file name “D:\Soft\Data\Data_Log.LDF” may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database ‘Lis’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
———————————————————————————————————————
Can you tell me what i do inorder to make my database read and write?
it is seem to be permission issue at windows level. kindly check the service account, give the permission read and write permission to all the folder of SQL server service account or windows login for which you have logged in SQL server instance.