Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


detach multiple databases using sp_detach_db


detach multiple databases using sp_detach_db

Author
Message
smokinphoenix2000
smokinphoenix2000
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
All I'm trying to detach multiple databases with the sp_detach_db. All the databases (about 20 of them) have a similar name like LM_04302008.
I was hoping to detach all of them in one shot by using a wild card (%) but its not working. How can I detach 20 databases in one shot with one query? I'm a rookie at this and have not found anything on line to help. Any help is appreciated.
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3563 Visits: 3104
You could try something like the following.

USE MASTER
declare databases cursor for select name from sysdatabases where name not in ('master', 'tempdb', 'msdb', 'model', ...any other databases you do not want detached)
declare @db sysname
declase @sql varchar (5000)
open databases

fetch next from databases into @db
while @@fetch_status = 0
begin
-- not 100% sure of the detach db syntax 'coz I do not have access to SQL where I am - it might be incorrect
set @sql = 'sp_detach_db ' + @db
exec (@sql)
fetch next from databases into @db

end
close databases
deallocate databases



vidhya sagar
vidhya sagar
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 620
You can use the below script to do the same. I've excluded system db's since the same can't be removed.

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity')
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
print 'sp_detach_db ' + @dbname
print 'go'
print 'print ''Detach of ' + upper(@dbname) + ' database successfully completed'''
print 'go'
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully detached'''



Copy the above script and execute it, you will get the output as detach script . Copy the output and execut it. All the user db's will be detached!!!!

If you face any problem let me know

Regards..Vidhya Sagar
SQL-Articles
Ed Bob
Ed Bob
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 13
Great time saver. I have to move mdf files for 198 databases and this is perfect. One thing I added was below Alter command to drop connections prior to detach.

print 'ALTER DATABASE ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

print 'go'

Also, since I'm reattaching DBs from new file location, I used your script and replaced print commands with below to attach the DBs. Just made sure tor run both scripts before detaching so you have the Detach and Attach scripts needed:

print 'USE [master]'
print 'GO'
print 'CREATE DATABASE ' + @dbname + ' ON'
print '( FILENAME = N'''+ 'E:\MSSQL2K5\Data\' + @dbname + '.mdf''' + '),'
print '( FILENAME = N'''+ 'F:\MSSQL2K5\log\' + @dbname + '_log.ldf''' + ')'
print 'FOR ATTACH'
print 'GO'
daniel_merck
daniel_merck
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 33
Hey,

What was the final script that you came up with...?

Daniel
Ed Bob
Ed Bob
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 13
Even better, If you're using SQL Server 2005 -- no need to detach, just set offline -- I posted script here:

http://mpdsharepoint.blogspot.com/2009/06/why-ask-dba-move-sharepoint-content.html
DCD
DCD
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 137
Ed Bob,
Thanks for the handy script. I have several servers with hundreds of dbs and need to reconfigure SAN drives.

DD
sri6sri6
sri6sri6
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
I ahve modified the querry as the execution satement was missing in it.

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity')
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
Exec sp_detach_db @dbname --- This Line was missing in the querry included that and it worked wonderfully
print 'go'
print 'print ''Detach of ' + upper(@dbname) + ' database successfully completed'''
print 'go'
PRINT ' '
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully detached'

any querries mail me @ sri6sri6@yahoo.com. I will try to provide my help ASAP.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47236 Visits: 44376
sri6sri6 (12/10/2012)
I ahve modified the querry as the execution satement was missing in it.


It wasn't missing. The script you refer to generated the sp_detach statements, allowing someone to copy the output, check it over and then run it.

p.s. 4 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search