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.
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
@Sunil –
Deepak has wrote an article for attaching user databases, you can get the script in http://sql-articles.com/scripts/attach-all-user-databases/ link