detach multiple databases using sp_detach_db

Question

Post reply

detach multiple databases using sp_detach_db

smokinphoenix2000

SSC Veteran

Points: 223
More actions
April 29, 2008 at 6:27 pm

#185896

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29386 More actions · Answer 1

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 SSCrazy Eights Points: 8216 More actions · Answer 2

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 SSC Enthusiast Points: 136 More actions · Answer 3

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 SSC Enthusiast Points: 104 More actions · Answer 4

Hey,

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

Daniel

Ed Bob SSC Enthusiast Points: 136 More actions · Answer 5

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 Valued Member Points: 54 More actions · Answer 6

Ed Bob,

Thanks for the handy script. I have several servers with hundreds of dbs and need to reconfigure SAN drives.

DD

sri6sri6 SSC Journeyman Points: 85 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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