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!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Administration Articles:
- Essential SQL Sever Administration Tips
- How to Patch a Standalone SQL Server Instance
- The SQL Server Browser Service and UDP Port 1434
- The Maximum Number of Concurrent Connections Setting in SQL Server
- Top 10 SQL Server DBA Daily Tasks List
- There is no SQL Server Failover Cluster Available to Join
- Encrypting a SQL Server Database Backup
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.