HELP...Need to create query to place multiple databases offline

Hi All,
I am currently working on a project on a sql server that has like 500
databases. I need to place like 80% of the databases offline. I have been
using dboption or EM but they are time comsuming since I have to do this one
database at a time. I have like 5 more servers with 500 databases to do this
on. Any way I can do this via script? IS there a away to perform the
sp_dboption to all databases with an exception to a few? Any help is greatly
appreciated.
Regards,
RichardSeveral ways...
One way is to have a table with either the exception databases or the other
way around. Then you
have a .sql script which does something like:
SELECT name FROM sysdatabases
WHERE name NOT IN (SELECT name FROM theExceptionTable)
AND NAME NOT IN('master', tempdb', 'msdb', model')
You create a cursor of above (see DECLARE CURSOR) and loop the cursor. Insid
e the cursor, you use
dynamic SQL to execute ALTER DATABASE command to set the database name offli
ne.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:2CA216A7-C93E-46D2-96C2-5B0E99A3E671@.microsoft.com...
> Hi All,
> I am currently working on a project on a sql server that has like 500
> databases. I need to place like 80% of the databases offline. I have been
> using dboption or EM but they are time comsuming since I have to do this o
ne
> database at a time. I have like 5 more servers with 500 databases to do th
is
> on. Any way I can do this via script? IS there a away to perform the
> sp_dboption to all databases with an exception to a few? Any help is great
ly
> appreciated.
>
> Regards,
> Richard|||sp_MSforeachdb 'USE ? IF DB_NAME() NOT IN '' ...exception db list...'' EXE
C
sp_dboption ?, ''read only'', ''true'''
"Richard" wrote:

> Hi All,
> I am currently working on a project on a sql server that has like 500
> databases. I need to place like 80% of the databases offline. I have been
> using dboption or EM but they are time comsuming since I have to do this o
ne
> database at a time. I have like 5 more servers with 500 databases to do th
is
> on. Any way I can do this via script? IS there a away to perform the
> sp_dboption to all databases with an exception to a few? Any help is great
ly
> appreciated.
>
> Regards,
> Richard