Detach User Databases

I started side by side upgrading SQL 2000 to SQL 2005, at that time I need to detach all the user databases from the source server and need to attach the same in destination server. Source server has almost 80 user databases and I feel bored to detach all the database one by one which is time consuming too.. Hence I thought of writing a script which will help me to detach the user databases in a single stretch. I know that someone will face this situation, hence I’m posting my script here which will help you!!

Script:

/*
Script to detach all user databases
Written by Vidhya Sagar
www.sql-articles.com
*/
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','distribution','repldata')
OPEN rs_cursor
FETCH NEXT FROM rs_cursor INTO @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No User databases found!!!'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exec VARCHAR(8000)
SET @exec= 'DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''''
SELECT @spidstr=coalesce(@spidstr,'','' )+''kill ''+convert(varchar, spid)+ ''; ''
FROM master..sysprocesses WHERE dbid=db_id('''+@dbname+''')
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id('''+@dbname+''')
END'+CHAR(10)+';'+CHAR(10)+
'Exec sp_detach_db ' + @dbname
EXEC (@exec)
PRINT 'Detach of ' + UPPER(@dbname) + ' database successfully completed'
PRINT ''
FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
DEALLOCATE rs_cursor
PRINT ' '
PRINT UPPER(@server_name) + '--> All User databases successfully detached'

Usage:

Copy the above script and execute it, this will automatically detach all the user databases and you will receive the output as above!!

 

Output:

Detach of NORTHWIND database successfully completed

Detach of PUBS database successfully completed

SAGARSYSSQL2K–> All User databases successfully detached

If you have any concurrence or discussion in the script above, lets discuss on SQL-Articles Forums section.

Leave a Reply

2 comments

  1. Great work buddy… Thanks a lot.

    Can you pls also give me the script that does not attach but prints out the attach Script selecting all the user databases and their files.

    your help is much appreciated

Leave a comment

Your email address will not be published. Required fields are marked *

*