InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:23:09 GMT20http://www.sqlservercentral.com/Forums/Topic492558-324-1.aspx[quote][b]sri6sri6 (12/10/2012)[/b][hr]I ahve modified the querry as the execution satement was missing in it.[/quote]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.Mon, 10 Dec 2012 06:59:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxI ahve modified the querry as the execution satement was missing in it.set nocount ondeclare @dbname as varchar(80)declare @server_name as varchar(20)select @server_name = @@servernamedeclare 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 @dbnameIF @@FETCH_STATUS <> 0 PRINT 'No database to backup...Please check your script!!!'WHILE @@FETCH_STATUS = 0BEGIN 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 @dbnameENDCLOSE rs_cursordeallocate rs_cursorprint ' '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.Mon, 10 Dec 2012 06:46:07 GMTsri6sri6http://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxEd Bob,Thanks for the handy script. I have several servers with hundreds of dbs and need to reconfigure SAN drives. DDWed, 16 Sep 2009 14:01:50 GMTDCDhttp://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxEven better, If you're using SQL Server 2005 -- no need to detach, just set offline -- I posted script here:[url=http://mpdsharepoint.blogspot.com/2009/06/why-ask-dba-move-sharepoint-content.html]http://mpdsharepoint.blogspot.com/2009/06/why-ask-dba-move-sharepoint-content.html[/url]Fri, 24 Jul 2009 23:40:52 GMTEd Bobhttp://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxHey, What was the final script that you came up with...?DanielMon, 06 Jul 2009 06:48:51 GMTdaniel_merckhttp://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxGreat 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'Mon, 22 Jun 2009 14:59:29 GMTEd Bobhttp://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxYou can use the below script to do the same. I've excluded system db's since the same can't be removed.[code]set nocount ondeclare @dbname as varchar(80)declare @server_name as varchar(20)select @server_name = @@servernamedeclare 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 @dbnameIF @@FETCH_STATUS <> 0 PRINT 'No database to backup...Please check your script!!!'WHILE @@FETCH_STATUS = 0BEGIN 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 @dbnameENDCLOSE rs_cursordeallocate rs_cursorprint ' 'print 'print ''SERVER NAME : ' + upper(@server_name) + '--> All databases successfully detached'''[/code]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 knowThu, 01 May 2008 19:22:08 GMTvidhya sagarhttp://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxYou could try something like the following. USE MASTERdeclare 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 sysnamedeclase @sql varchar (5000)open databasesfetch next from databases into @dbwhile @@fetch_status = 0begin-- 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 @dbendclose databasesdeallocate databasesTue, 29 Apr 2008 22:27:05 GMThappycat59http://www.sqlservercentral.com/Forums/Topic492558-324-1.aspxAll 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.Tue, 29 Apr 2008 18:27:36 GMTsmokinphoenix2000