Sunday, March 26, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - Attach and Detach Databases

By Saleem Hakani on 8/25/2014

This script allows you to generate script to detach and attach one or all databases from your local SQL Server instance. This can be very useful when performing maintenance, upgrade, backups of files, relocating data/log files to other drives, etc. You no longer need to remember the long hard-coded file path after detaching the database since this procedure takes care of generating the entire command along with the path for you.

This script also takes care of the 16 file limit when using SP_Attach_DB system stored procedure. Please follow the help for successful implementation of this script.

--Author: Saleem Hakani (Http://sqlcommunity.com)

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

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

 

 

 

 

 

 

 

 

Attach and Detach Databases
Attach database
Create Database For Attach
Detach database
Moving a Database
Relocating Database Files
sp_attach_db
sp_detach_db
Upgrading a database
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM