Solved

How do i take all SQL databases offline via a script?

Posted on 2010-08-17
4
3,891 Views
Last Modified: 2012-05-10
Hi Experts,

I have 2 SQL instances - x1 SQL 2000 and x1 SQL 2005.
Each instance has about 100 databases in it.

I need a way to quickly take all the databases offline.
I have tried the query:

ALTER DATABASE database-name SET OFFLINE WITH NO_WAIT

However, this only takes the individual database offline. I would like a way to take all the databases in an instance offline from a script.

Is this possible?

thanks
Nick
0
Comment
Question by:statpro
  • 2
4 Comments
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 125 total points
ID: 33453601
use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET OFFLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)
0
 

Author Comment

by:statpro
ID: 33454527
Hi,

Awesome - worked perfectly with SQL 2005 (and tested with 2008 also).

Didn't work with SQL 2000.
I get error message:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'max'.
Server: Msg 137, Level 15, State 1, Line 3
Must declare the variable '@nsql'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@nsql'.

Is there any way to tweak to work with SQL 2000 instance?
Many thanks.
0
 
LVL 3

Accepted Solution

by:
PrakashRaoBS earned 125 total points
ID: 33454619
the following code will work perfectly for sql server 2000.
USE MASTER; 
DECLARE @nsql NVARCHAR(4000); 

SELECT @nsql = Coalesce(@nsql + CHAR(13) + CHAR(10), '') + 'ALTER DATABASE [' + name + '] SET OFFLINE WITH NO_WAIT;' 
FROM   MASTER..sysdatabases 
WHERE  sid <> 0x01 

PRINT @nsql 
EXEC (@nsql)

Open in new window

0
 

Author Comment

by:statpro
ID: 33454814
Awesome - SQL 2000 script also works - thanks guys
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question