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
If a table has been locked in database db.How to display the table on screen?Which query should be used
I have read lot of your posts. very helpful. good work, dude. God bless you
Excellent, thank you!
As I’m not a SQL guy can you also set an SQL Instance to be read-only or do you have to choose the database itself?
Excelente, muchas gracias
Excellent post Pinal. I’m working with Azure SQL DB and can use this command from Mgmt console. Would you happen to know if there is an Azure API to do the same programmatic-ally?
Very nice….
very nice
I was using a database in IE 9, but since the upgrade to IE 10 it opens in Read Only mode, i have taken off the enhanced security features in IE10 but still cannot open it correctly. any ideas?
Database in IE? Did you mean Internet Explorer? I am not clear with the question.
I made the database read only because don’t want the user to add any data on it. I do exactly what you write above, it works. Thanks.
Could you please also write HOW you set the DB to STANDBY / READONLY?
Can we replicate the DB in read/write mode? If not,is there any other way to make it available in read-write mode?
Thanks for the post. Is it possible to allow only few tables like UserLogin as writable and allow other table to be read only?
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 9001, Level 21, State 1, Line 1
The log for database is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Run a DBCC on the database that you get that error, you have issue with your log files that you need to rebuild/repair. once resolved you can run the alter statement
Thank You so much! your posts are always helpful.
May God bless you with lot of success.
How do you reference the current database rather than have to type the database name?
I have configured PR DR transactional logshipping. If PR server crush in that case how can I bring the DR(standby databe) in production
I created a job to take database read only but I fails and the reason is the agent user does not have permission to Alter database. when I give the permission to the Agent user, the job fails again and the permission get disappeared.
Msg 5063, Level 16, State 1, Line 3
Database ‘TestDB’ is in warm standby. A warm-standby database is read-only.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Ah yes. Here’s a new situation (for me). The production database will be subject to a planned power outage. It supports a read-only Standby database at an alternate location. I anticipate being able to set the database to READ-WRITE. But, at the end of t he power outage, I expect they are going to want to return to the previous configuration. What will be the process to update the original primary database with all of the data process since the cut-over?