Generate Script To Detach All User Databases

Generate Script To Detach All User Databases on SQL Server

Our online SQL tutorials teach you how to detach a database in SQL Server using Transact-SQL or SQL Server Management Studio. The detached files are kept in the file system and are not removed. The CREATE DATABASE... FOR ATTACH or FOR ATTACH REBUILD LOG options can be used to reattach the files. The files may also be transferred to a different server and then attached to a similar-version or newer instance.

Using SSMS

1.     In SQL Server Management Studio Object Explorer, connect to the instance of the SQL Server Database Engine and then expand the instance.

2.     Expand Databases, and select the name of the user database you want to detach.

3.     Right-click the database name, point to Tasks, and then select Detach. The Detach Database dialog box appears.

Using T-SQL Scripts:

USE [SampleDB]

GO 

SELECT type_desc, name, physical_name from sys.database_files;

 

EXEC sp_detach_db 'AdventureWorks2012', 'true';  

The following code can be used to create a script that would detach every user database on SQL Server.

This can be useful if we need to disconnect every SQL Server database from one server and attach to another SQL Server during migration. To detach all databases, copy the script's output and run it in SSMS after it has been generated.

DECLARE @DatabaseName AS VARCHAR(500)

DECLARE DetachCur CURSOR FOR

  SELECT name

  FROM   MASTER.sys.databases

  WHERE  owner_sid > 1;

OPEN DetachCur

FETCH Next FROM DetachCur INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

  BEGIN

      PRINT 'sp_detach_db ' + @DatabaseName + CHAR(10)

            + ' GO' + CHAR(10) + 'Print ''Detach of '

            + @DatabaseName

            + ' database completed successfully'''

            + CHAR(10) + ' GO'

 

      FETCH Next FROM DetachCur INTO @DatabaseName

  END

 

CLOSE DetachCur

DEALLOCATE DetachCur