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

47 Comments. Leave new

  • Akshay Jadhav
    July 8, 2022 10:40 pm

    What is the difference between sending readOnly in connection while making SQL connection and using NOLOCK ?
    If we are interested only in reading the data from SQL without blocking anything in the server what approach we should take ?

    Reply

Leave a Reply