Solved

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

Posted on 2010-08-17
4
3,356 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Awesome - SQL 2000 script also works - thanks guys
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now