sp_dboption

Question

Post reply

sp_dboption

TST1

SSCommitted

Points: 1798
More actions
July 20, 2011 at 4:45 am

#241107

Hi,
I'm having a bit of trouble working out how I can script this to get all the databases offline apart from the system dbs.
exec sp_MSforeachdb 'USE ? IF DB_NAME() NOT IN " " EXEC
sp_dboption ?, ''read only'', ''true'''
Any assistance much appreciated

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 1

exec sp_msforeachdb 'use [?]

if db_name() not in (''tempdb'')

begin

alter database <> set read_only

end'

however, there should be no active connections.

Cheers

Jannie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jannie-186227 (7/20/2011)
exec sp_msforeachdb 'use [?]
if db_name() not in (''tempdb'')
begin
alter database <> set read_only
end'
however, there should be no active connections.
Cheers
Jannie

I don't think you want to try setting the system databases readonly....

exec sp_msforeachdb 'if db_id(''?'') > 4 alter database [?] set read_only with rollback immediate'

That'll also roll back any active transactions in the DBs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TST1 SSCommitted Points: 1798 More actions · Answer 3

Gail,

I get an error when I run the example you've given.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I tested it and it works. Just watch that the forum converted > to > (and that will give an error). Fixed now. If you have that, either copy again or manually change it back to what it should be.

p.s. you are using SQL 2008, right?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

TST1 (7/20/2011)
Hi,
I'm having a bit of trouble working out how I can script this to get all the databases offline apart from the system dbs.
exec sp_MSforeachdb 'USE ? IF DB_NAME() NOT IN " " EXEC
sp_dboption ?, ''read only'', ''true'''
Any assistance much appreciated

You can get that info from sys.databases

select * from sys.databases where is_read_only = 1

Leo.Miller SSChampion Points: 13051 More actions · Answer 6

If this is something you want to use going forward then avoid the sp_dboption and stick to the ALTER DATABASE version.

sp_dboption has been removed in SQL Denali.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Leo.Miller (7/21/2011)
If this is something you want to use going forward then avoid the sp_dboption and stick to the ALTER DATABASE version.
sp_dboption has been removed in SQL Denali.
Cheers
Leo

Yes the use of ALTER DATABASE was a favorite in many of the post, why go with something that is going away?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/