Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

detach multiple databases using sp_detach_db Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2008 6:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2008 8:15 PM
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.



Post #492558
Posted Tuesday, April 29, 2008 10:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:37 PM
Points: 3,005, Visits: 2,606
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



Post #492616
Posted Thursday, May 1, 2008 7:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:31 PM
Points: 416, Visits: 573
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
Post #493979
Posted Monday, June 22, 2009 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 24, 2009 11:39 PM
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'
Post #739764
Posted Monday, July 6, 2009 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 8, 2013 12:13 PM
Points: 4, Visits: 33
Hey,

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

Daniel
Post #747665
Posted Friday, July 24, 2009 11:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 24, 2009 11:39 PM
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
Post #759578
Posted Wednesday, September 16, 2009 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 11:36 AM
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
Post #789256
Posted Monday, December 10, 2012 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 28, 2013 10:29 PM
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.
Post #1394583
Posted Monday, December 10, 2012 6:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
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 2008, MVP
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

Post #1394595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse