SQL Server Software Tools, Online Courses. eBooks, Articles and Webcasts - SQLNetHub

Massively Detaching and Re-attaching Databases in SQL Server

by

In this article, we will be discussing about massively detaching and re-attaching databases in SQL Server.

 

Why massively detaching and re-attaching all the databases in a SQL Server instance?

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).

 

Helpful T-SQL Generation Scripts for Detaching and Re-Attaching all the Databases

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

* Note: The scripts presented in this article are for only for showing a different way of doing things. In any case, if you use them, 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. 

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.

 

Attach DDL Statements Generation Script

--


--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 Script

--
--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

 

Check our Master Class “Essential SQL Server Administration Tips”

If you really want to learn sophisticated SQL Server administration techniques, then you should check our on-demand online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Related SQL Server Administration Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 5,852