Solved

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

Posted on 2010-08-17
4
3,721 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

810 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