Attaching and Detaching SQL Server Databases

DBAs and Developers at times may need to detach and attach the databases for various reasons including the following:

1. Moving files to other drives
2. Server migration
2. Upgrades,
3. system maintenance, etc.

This script takes away the headache of remembering the filename or folder location of every single data or log file for every single user databases on your server. It allows you to generate script to detach and attach one or all databases avoiding the need to remember the long hard-coded file path after detaching the database.

CREATE Procedure [dbo].[SQLOPS_AttachDETACH] (@Cmd Varchar(20)=null, @DBName Varchar(50)='ALL') as
Set NOCOUNT ON
--------------------------------------------
--Author: Saleem Hakani (Http://sqlcommunity.com)
--Compatible with SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017 +
--Desc: This procedure can be used to generate detach and attach script for one or all the databases
--Note: This SPROC also takes care of the 16 file limitation with SP_Attach_DB system stored proceure.'
--------------------------------------------
If (@Cmd is null or @Cmd <> 'Execute')
Begin
	Print 'HELP for SQLOPS_AttachDETACH'
	Print '============================'
	Print 'Author: Saleem Hakani (Http://sqlcommunity.com)'
	Print ' '
	Print 'This procedure can be used to generate detach and attach script for one or all the databases.'
	Print 'It also takes care of the 16 files limitation by that SP_attach_db system sproc has.'
	Print ' '
	Print 'SYNTAX: Exec SQLOPS_AttachDETACH <@cmd>, <@DBName>'
	Print '@cmd: Can be either ''Help'' (to get help) or ''Execute'' (to execute this sproc)'
	Print '@DBName: Can either be the name of the database or ''ALL'' (default) which means run this on all databases'
	Print ' '
	Print 'Example: To generate a script for attaching and detaching a specific database (Ex: SQLOPS database)'
	Print 'Exec SQLOPS_AttachDETACH ''Execute'',''SQLOPS'';'
	Print ' '
	Print 'Example: To generate a script for attaching and detaching all user databases from local SQL Server'
	Print 'Exec SQLOPS_AttachDETACH ''Execute'',''ALL'''
	Print ' '
	Print 'To obtain help'
	Print 'Exec SQLOPS_AttachDETACH ''HELP'''
	Return
End
If (@cmd='Execute' and @DBName='All')
Begin
	Exec SP_MSForEachDB 'Exec [SQLOPS_AttachDETACH] ''Execute'',[?]'
	RETURN
End
 
If (@cmd='Execute' and @DBName <> 'All')
Begin
	If NOT EXISTS (SELECT NAME from Sys.Databases Where Name=@DBName)
	Begin
		Print 'Database ['+@DBName+'] does not exist. Please enter a valid database name.'
		RETURN
	End
 
	If Exists (SELECT NAME from Sys.Databases Where Name=@DBName)
	Begin
		Declare @a Varchar(8000),@b Varchar(8000),@c Varchar(8000),@d Varchar(8000)
		Declare @FileID Int,@str int,@Count1 int
		Create Table ##Count1 (Count1 int)
		Declare @FileName Varchar(500)
		Select @str=0
		Print '--------------------------------------------------------------------'
		Print ''
		Print 'SP_Detach_DB ['+Ltrim(@DBName)+']'
		Print 'Go'
		Print ''
		Print 'Create Database ['+Ltrim(@DBName)+'] ON PRIMARY'
		Exec('Insert into ##count1 Select Count(*) from '+@DBName+'..SysFiles')
		Select @Count1=Count1 from ##Count1
		Exec('Declare CheckTab Cursor For Select FileID,FileName from '+@DBName+'..SysFiles Order by FileID')
		Open CheckTab
		Fetch Next from CheckTab into @FileID,@FileName
		While (@@Fetch_Status=0)
		Begin
		Select @Str=@str+1
 
		If @Str < @Count1
		Begin
		Select @b='(FileName='''+Ltrim(Rtrim(@FileName))+'''),'
		End
 
		If @Str = @Count1
		Begin
		Select @b='(FileName='''+Ltrim(Rtrim(@FileName))+''') FOR ATTACH'
		End
		Print @b
	
		Fetch Next from CheckTab into @FileID,@FileName
		End
		Print 'Go'
		Drop Table ##Count1
		Close CheckTab
		Deallocate CheckTab
	End
	RETURN
END
GO