Solved

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

Posted on 2010-08-17
4
4,068 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

730 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