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

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)