Script Database for “Create For Attach”

Background

In days gone by we used sp_attach_db when we need to re-attach database data and log files previously detached using sp_detach_db.

In the world of today, MSFT recommends using “create database for attach“.

Written below is a Stored Procedure that will review a currently attached database and script out its file references in preparation for file relocation, etc.

Code

dbo.sp_ScriptDatabaseCreateForAttach

Script

 




use [master]
go

if object_id('[dbo].[sp_ScriptDatabaseCreateForAttach]') is null
begin

	exec('create procedure [dbo].[sp_ScriptDatabaseCreateForAttach] as ')
end
go

alter procedure [dbo].[sp_ScriptDatabaseCreateForAttach]
(
	@dbName sysname
)
as
begin

	set nocount on;

	declare @dbID	int

	declare @CHAR_QUOTE	 char(1)
	declare @CHAR_COMMA  char(1)
	declare @CHAR_CRLF   varchar(10)
	declare @CHAR_BRACKET_CLOSE char(1)

	declare @log varchar(255)

	declare @FORMAT_DATABASE_IS_NOT_AVAILABLE varchar(600)
	declare @sql varchar(600)

	set @CHAR_QUOTE = ''''
	set @CHAR_COMMA = ','
	set @CHAR_CRLF = char(13) + char(10)
	set @CHAR_BRACKET_CLOSE = ')'

	set @FORMAT_DATABASE_IS_NOT_AVAILABLE = 'Database (%s) is not available'

	set @dbID = db_id(@dbName)

	if (@dbID is null)
	begin
	
		exec master.dbo.xp_sprintf
			  @log output
			, @FORMAT_DATABASE_IS_NOT_AVAILABLE
			, @dbName

		raiserror(@log, 16, 1)
	
	end	

	; with cte
	(
		[clause]
	)
	as
	(

		select
			' CREATE DATABASE '
			+ quoteName(@dbName)
			+ ' ON ' +
			@CHAR_CRLF

		union all 

		select
			stuff
				((
					select 
							@CHAR_COMMA
							+ '(NAME=' 
							+ @CHAR_QUOTE
							+ tblMF.[name] 
							+ @CHAR_QUOTE
							+ @CHAR_COMMA
							+ ' FILENAME=' 
							+ @CHAR_QUOTE
							+ tblMF.[physical_name] 
							+ @CHAR_QUOTE
							+ ')'

					from   sys.master_files tblMF

					where  tblMF.type_desc != 'LOG'

					and    tblMF.database_id = @dbID

					FOR XML PATH('')
					)
					 ,1 , 1, ''
				)
		union all

		select
			' LOG ON '

		union all


		select
			stuff
				((
					select 
					  @CHAR_COMMA
					+ '(NAME=' 
					+ @CHAR_QUOTE
					+ tblMF.[name] 
					+ @CHAR_QUOTE
					+ @CHAR_COMMA
					+ ' FILENAME=' 
					+ @CHAR_QUOTE
					+ tblMF.[physical_name] 
					+ @CHAR_QUOTE
					+ ') '

			from   sys.master_files tblMF

			where  tblMF.type_desc = 'LOG'

			and    tblMF.database_id = @dbID

					FOR XML PATH('')
					
					)
					 ,1 , 1, ''
				)

		union all

		select
			' FOR ATTACH '

	)


	select 
			  [clause]
			= replace
				(
					  cte.clause
					, @CHAR_BRACKET_CLOSE
					, @CHAR_BRACKET_CLOSE +  @CHAR_CRLF
				)

	from   cte

end
go


Invoke


declare @dbName sysname

set @dbName = 'rbpivr1'

exec dbo.sp_ScriptDatabaseCreateForAttach
	@dbName = @dbName

 

Output


 CREATE DATABASE [rbpivr1] ON 
(NAME='rbpivr1', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1.mdf')
,(NAME='rbpivr11', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_0.ndf')
,(NAME='rbpivr12', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_1.ndf')
,(NAME='rbpivr13', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_2.ndf')
,(NAME='rbpivr14', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_3.ndf')
,(NAME='rbpivr15', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_4.ndf')
,(NAME='rbpivr16', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_5.ndf')
,(NAME='rbpivr17', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_6.ndf')
,(NAME='rbpivr18', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\\rbpivr1_7.ndf')
,(NAME='memopt', FILENAME='M:\MSSQL12.MSSQLSERVER\MSSQL\DATA\rbpivr1_9.memopt')
 LOG ON 
--(NAME='rbpivr1_log', FILENAME='L:\MSSQL12.MSSQLSERVER\MSSQL\Data\\rbpivr1_8.ldf')
 (NAME='rbpivr1_log', FILENAME='Z:\MSSQL12.MSSQLSERVER\MSSQL\Log\\rbpivr1_8.ldf')
 FOR ATTACH 

Hint

Results To \ Results To File

BTW, when generating the SQL Script you likely want to utilize the “Results To” \ “Results To File“.

scripttofile

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s