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

I recently received following comments on my earlier blog:

“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 updation. is there any query available to resolve this problem. Your help will be highly helpful.”

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 error that if 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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

29 thoughts on “SQL SERVER – Making Database to Read Only – Changing Database to Read/Write

  1. 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.

  2. 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

  3. Use this one : restore database [dbname] with recovery
    and used sp_removedbreplication ‘Dbname’ to remove replication .

  4. 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?

  5. I am getting error Database state cannot be changed while other users are using the database, ALTER DATABASE statement failed.

  6. 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. ??

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

  8. 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.

  9. 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).

  10. 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.

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

  12. 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.

  13. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s