• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5229
  • Last Modified:

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

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
statpro
Asked:
statpro
  • 2
2 Solutions
 
cyberkiwiCommented:
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
 
statproAuthor Commented:
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
 
PrakashRaoBSCommented:
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
 
statproAuthor Commented:
Awesome - SQL 2000 script also works - thanks guys
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now