Detach And Attach Database in SQL Server | Detach multiple databases using sp_detach_db | detach attach database sql server

This article describes,

Detach And Attach Database in SQL Server or Detach multiple databases using sp_detach_db

What happens when you detach a SQL database?

If you want to move database from one SQL server instance to another SQL Server instance, we can use detach method. We can use SSMS to detach database from SQL Server.

But if we have to detach multiple databases, using SSMS is time consuming and tedious task

How to massively detach database?

If you want to detach 100+ databases from SQL server to move it to another SQL instance, we need to generate SQL script using sp_detach_db system stored procedure.

Steps to create SQL Script to detach all databases from SQL Server instance.

Step 1: Connect to SQL Server instance and open SSMS.

Step 2: Go to Tools tab in SSMS and select Option.

Step 3 : Select Query Result option and then go to SQL Server and Result to Grid.

Step 4 : Enable check box Retain CR/LF on copy or save and click OK. This feature is required to add new tab in script to avoid syntax error during execution.

Get Backup History of SQL Server: Backup history SQL server| Query to check backup history in SQL server

Detach And Attach Database in SQL Server or Detach multiple databases using sp_detach_db

Step 5: Once above step completed, Open new query in SSMS and copy and paste below query. This script has

DECLARE @dbName varchar(255); -- To store database name
DECLARE DBCURSOR CURSOR FOR
SELECT name FROM sys.databases WHERE database_id not in(1,2,3,4) -- If you want to dettach all databases remove where condition
-- All user databases 
OPEN DBCURSOR FETCH Next from DBCURSOR INTO @dbName
WHILE @@FETCH_STATUS= 0 BEGIN
PRINT'EXEC sp_detach_db ' + @dbName + CHAR(10)
-- CHAR(10) for newline
+'GO' + CHAR(10) +'Print ''Detach of ' + @dbName + ' database completed successfully''' +CHAR(10)+'GO'
FETCH NEXT FROM DBCURSOR INTO @dbName END 
CLOSE DBCURSOR   
DEALLOCATE DBCURSOR

Step 6: Execute query on SQL server instance from you want to detach databases. Script will generate detach script for all databases as per filter in query. Copy and paste detach script and copy it to new query winnow and execute to detach all database at one place.

Detach Script look like this:

EXEC sp_detach_db AdventureWorks2019
GO
Print 'Detach of AdventureWorks2019 database completed successfully'
GO
EXEC sp_detach_db AdventureWorksDW2019
GO
Print 'Detach of AdventureWorksDW2019 database completed successfully'
GO
EXEC sp_detach_db AdventureWorksLT2019
GO
Print 'Detach of AdventureWorksLT2019 database completed successfully'
GO

If you want to get file list from respective folder using SQL script, Click here