Detach all databases

---Detach all databases
set nocount on
declare @dbname sysname,@string varchar(400)
declare dbcr cursor
 for  select [name] from sys.sysdatabases where [name] <> 'msdb' and [name] <> 'master' and [name] <> 'model' and [name] <> 'tempdb'
OPEN dbcr
FETCH next
    FROM  dbcr into   @dbname
WHILE (@@FETCH_status= 0)
BEGIN
    select @string =  'sp_detach_db ' + ''''+@dbname+''',' + '''true'''     
    print @string
    print 'go'
 FETCH next
    FROM dbcr into @dbname
end
close dbcr
deallocate dbcr


------------------
AFTER APPLYING ABOVE NO OF USER STORED PROCEDURE WILL BE CREATED         WE CAN DETACH MULTIPLE USER DBS USING THE ABOVE SYENTEXT
WE CAN USE THE SAME AND EXECUTE THE SP_DETACH_DB

EXAMPLE LIKE BELOW


sp_detach_db 'Northwind','true'
go
sp_detach_db 'UNION','true'
go

-----IF DB IN EMERGENCY MODE IF WE DONT LIKE AGAIN ADD LOG FILE WE CAN USE THIS DETACH AND ATTACH THE DB TO THE SAME SERVER

UPDATE master..sysdatabases
        SET status = 32768
        WHERE name = 'UNION'

sp_detach_db 'Northwind','true'
go
sp_detach_db 'UNION','true'
go

Attach all databases

Run this proc to create the output to attach all databases
Copy the output to a new query and execute it




--Attach all databases to
--F:\mssql\data
--E:\mssql\data


set nocount on
declare @dbname sysname,@string nvarchar(600),@f1 varchar(100), @f2 varchar(100)
declare @d varchar(200),@len int,@totlen int,@x int
declare @parmdef  nvarchar(200)
declare dbcr cursor
 for  select [name] from sys.sysdatabases where [name] <> 'msdb' and [name] <> 'master' and [name] <> 'model' and [name] <> 'tempdb'
OPEN dbcr
FETCH next
    FROM  dbcr into   @dbname
WHILE (@@FETCH_status= 0)
BEGIN
     select @string = N'use ' + @dbname + ';  select @f1=[filename] from sysfiles where fileid = 1;'  
     set @parmdef =N'@f1 varchar(200) output'
     EXECUTE SP_EXECUTESQL @string,@parmdef,
             @f1=@f1 output
     select @string = N'use ' + @dbname + ';  select @f2=[filename] from sysfiles where fileid = 2;'  
     set @parmdef =N'@f2 varchar(200) output'
     EXECUTE SP_EXECUTESQL @string,@parmdef,
             @f2=@f2 output
   select @totlen = datalength (@f1)
   select @len = datalength (@f1) - charindex('\',reverse( @f1)) + 1
   select @len 
   select @x = @totlen - @len
   select @d = right(@f1,@x)   
   select @string =  'sp_attach_db @dbname=N' + ''''+@dbname+'''' + ',@filename1=N' +''''+ 'F:\mssql\data\'+ @d +''''
   select @totlen = datalength (@f2)
   select @len = datalength (@f2) - charindex('\',reverse( @f2)) + 1
   select @len 
   select @x = @totlen - @len
   select @d = right(@f2,@x)   
   select @string =  @string + ',@filename2=N' +''''+ 'E:\mssql\data\'+ @d + ''''     
   print @string
   print 'go'
 FETCH next
    FROM dbcr into @dbname
end
close dbcr
deallocate dbcr

attach and dettach databases

/* sql 2000
This script moves databases to different physical locations. This script can also be used to just detach/attach user databases as part of your bigger process.

.

*/



DECLARE @Path varchar(400)
SET @Path = "D:\MSSQL\Data\"
use [master]
--STEP 1
--Keep the output of this script aside
Select "EXEC sp_attach_db @dbname = ''"+ [name] +"''," +
     "@filename1 = ''" + @Path +  [name] +"_Data.mdf''," +
     "@filename2 = ''"+ @Path + [name] +"_Log.ldf''" from sysdatabases
         where dbid > 4

--STEP 2
--Generate this script and run
Select ''EXEC sp_detach_db '' + [name]  from sysdatabases where dbid > 4

--After Successful run from Output of STEP 2 RUN Output of STEP 2