Massively Detaching and Re-attaching Databases in SQL Server

There are cases where you might need to massively detach and re-attach all the databases in a SQL Server instance. Such scenario can be a side-by-side upgrade of SQL Server on the same server/PC. In the case where you have 2-3 user databases on the instance it might not be an issue detaching and re-attaching the databases on the new instance. However, imagine having over 100 databases. It would be not the easiest thing to go and detach/re-attach each database one-by-one (keep in mind that you have to specify the data/log files for each database when you are attaching it).

In order to simplify things, I have developed a simple procedure that undertakes generating the necessary detach/attach scripts when you want to massively perform these actions on a SQL Server 2005 or later instance.

* Note: This procedure is presented in this article only for showing a different way of doing things. In any case where it could be used, a full backup of all the involved databases should be taken. Even though this method might be faster from backup/restore, the latter is much safer and preferable especially on Production environments. 

The procedure for generating the scripts is the following:

  1. Run the “Attach DDL Statements Generation Scrip”
  2. Run the “Detach DDL Statements Generation Script”

If you want to proceed and detach all user databases you can then execute the set of DDL statements generated by the “Detach DDL Statements Generation Script”.

If you want to attach the databases on the same instance or any other SQL Server instance on the same server/PC you can then execute the set of DDL statements generated by the “Attach DDL Statements Generation Scrip”.

You can view the scripts below. You can also download it.

Attach DDL Statements Generation Scrip


--
--Attach DDL Statements Generation Scrip
--Author: Artemakis Artemiou
--

print '--'
print '--Script for Attaching all DBs in a SQL Server Instance'
print '--'
print ''
SET NOCOUNT ON

DECLARE @dbname nvarchar(128)

DECLARE DBList_cursor CURSOR FOR 
select [name] from master.sys.databases where database_id > 4
OPEN DBList_cursor

FETCH NEXT FROM DBList_cursor 
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
declare @attach_TSQL_script varchar(max)
set @attach_TSQL_script=''
set @attach_TSQL_script=@attach_TSQL_script+'CREATE DATABASE ' + @dbname +' ON ' 

declare @tsql varchar(max),@filename varchar(max)
set @tsql='DECLARE DBFiles_cursor CURSOR FOR select [filename] from '+ @dbname + '.sys.sysfiles'
execute (@tsql)

PRINT '--'+@dbname

OPEN DBFiles_cursor
FETCH NEXT FROM DBFiles_cursor INTO @filename

WHILE @@FETCH_STATUS = 0
BEGIN
set @attach_TSQL_script=@attach_TSQL_script+' (FILENAME = '''+ @filename +'''),' 
FETCH NEXT FROM DBFiles_cursor INTO @filename
END

 set @attach_TSQL_script=SUBSTRING(@attach_TSQL_script,0,len(@attach_TSQL_script))
set @attach_TSQL_script=@attach_TSQL_script+' FOR ATTACH;'

PRINT @attach_TSQL_script
PRINT ''

CLOSE DBFiles_cursor
DEALLOCATE DBFiles_cursor

FETCH NEXT FROM DBList_cursor 
INTO @dbname

END 

CLOSE DBList_cursor
DEALLOCATE DBList_cursor

Detach DDL Statements Generation Scrip
--
--Detach DDL Statements Generation Scrip
--Author: Artemakis Artemiou
--

PRINT '--' 
PRINT '--Script for Detaching all DBs in a SQL Server Instance' 
PRINT '--' 
PRINT '' 

SET nocount ON 

DECLARE @dbname nvarchar(128) 
DECLARE dblist_cursor CURSOR FOR 
  SELECT [name] 
  FROM   master.sys.databases 
  WHERE  database_id > 4 

OPEN dblist_cursor 
FETCH next FROM dblist_cursor INTO @dbname 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
   print 'EXEC sp_detach_db ''' + @dbname + ''', ''true'';'
  FETCH next FROM dblist_cursor INTO @dbname 
  END 

CLOSE dblist_cursor 
DEALLOCATE dblist_cursor 

Download the scripts
You can download the scripts from the links below:

Labels: ,