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 to Sunil Cancel reply