easy question - taking db out of read only mode
-
Ok, maybe my search skillz in BOL need help but I can't seem to find this info out, if someone could shoot me the tsql syntax, i would appreciate it.
Ok so we are log shipping (well, testing it now) so on the secondary the db is in read only mode (the option we'd like to use). When the primary goes down, we'll need to take the db on the secondary server out of read only mode since it will be the production db at that point.
What do I need to run to get that db out of "read only" mode?
-
EXEC sp_dboption 'nameofdatabase', 'read only', 'FALSE'
Francis
-
You can also look under Alter database for more pointers
. -
I did.
The problem is that I'm finding several ways to do this. Which is fine as long as they are all valid, all lead to the same results and so forth. Here are some suggestions I've received and I keep finding more:
1. Fracis' suggestion above (didn't work, it said it was a warm spare)
2. Restore database 'databasename' with Recovery (seems to work the best)
3. The lengthy process of switching roles (although the assumption is that the primary wouldn't be up so I couldn't run the SP on the primary box first, as listed in BOL).
4. Using the alter database to open as readwrite (didn't work either)
-
See http://www.sql-server-performance.com/sql_server_log_shipping.asp for some more information on log-shipping including some scripts and details.
EXEC sp_dboption 'nameofdatabase', 'read only', 'FALSE'
works but you can't run this command if you haven't fully restored the database. You must recover the final log WITH RECOVERY first.
Francis
-
Hi,
this works for me:
use master
declare @DBN sysname
set @DBN = 'test'
exec master..kill_users @DBN --sproc to kill all users using the db...
RESTORE DATABASE @DBN WITH RECOVERY
EXEC SP_DBOPTION @DBN, 'read only', 'false'
EXEC SP_DBOPTION @DBN, 'dbo use only', 'false'
regards karl
Best regards
karl
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply