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)
Excelent Post.Keep it up Man
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.
Good one
IT IS REAL WORKING , VERY GOOD
THANK YOU MR.
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
Use this one : restore database [dbname] with recovery
and used sp_removedbreplication ‘Dbname’ to remove replication .
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?
I am getting error Database state cannot be changed while other users are using the database, ALTER DATABASE statement failed.
You need to make sure that the database is not accessed by other users
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. ??
Great, thanks for script…
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
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.
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).
i performe all the query ..but still the problem occurs
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.
can you put database in to standby mode? by Queries…
how can we set database in standby mode?
Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority