SQL SERVER – Making Database to Read Only – Changing Database to Read/Write

SQL SERVER - Making Database to Read Only - Changing Database to Read/Write secure-album 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)

, , ,
Previous Post
SQL SERVER – Finding Location of Log File when Primary Datafile is Crashed
Next Post
SQL SERVER – Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction

Related Posts

46 Comments. Leave new

  • If a table has been locked in database db.How to display the table on screen?Which query should be used

    Reply
  • Devaprakash Ganapathi
    February 22, 2014 9:42 pm

    I have read lot of your posts. very helpful. good work, dude. God bless you

    Reply
  • Mohan Chhapadia
    May 14, 2014 6:44 pm

    Excellent, thank you!

    Reply
  • 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?

    Reply
  • Excelente, muchas gracias

    Reply
  • 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?

    Reply
  • Very nice….

    Reply
  • very nice

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • Could you please also write HOW you set the DB to STANDBY / READONLY?

    Reply
  • Can we replicate the DB in read/write mode? If not,is there any other way to make it available in read-write mode?

    Reply
  • Thanks for the post. Is it possible to allow only few tables like UserLogin as writable and allow other table to be read only?

    Reply
  • 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.

    Reply
    • 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

      Reply
  • Thank You so much! your posts are always helpful.
    May God bless you with lot of success.

    Reply
  • How do you reference the current database rather than have to type the database name?

    Reply
  • I have configured PR DR transactional logshipping. If PR server crush in that case how can I bring the DR(standby databe) in production

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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?

    Reply

Leave a Reply

Menu