Script to produce SP_ATTACH_DB code

• russellgh

  SSC Enthusiast

  Points: 141

  More actions

  October 2, 2007 at 8:40 am

  #177875

  Comments posted to this topic are about the item Script to produce SP_ATTACH_DB code

• David Benoit

  SSC-Dedicated

  Points: 34562

  More actions

  May 7, 2009 at 2:45 pm

  #990699

  Found that the script only works if the fileid's are actually sequential. I have modified this so it works with the following code.

  IF EXISTS

  (SELECT name FROM sys.sysobjects WHERE name = 'USP_ATTACHDB')

  DROP PROCEDURE USP_ATTACHDB

  GO

  CREATE PROC USP_ATTACHDB

  @db SYSNAME = NULL

  AS

  SET nocount ON

  SET concat_null_yields_null OFF

  DECLARE @cmd VARCHAR(1000)

  DECLARE @a VARCHAR(2000)

  DECLARE @Filecnt INT

  DECLARE @cnt INT

  DECLARE @sq CHAR(1)

  DECLARE @dq CHAR(2)

  DECLARE @TempFilename VARCHAR(1000)

  DECLARE @TempFilename1 VARCHAR(1000)

  SET @sq = ''''

  SET @dq = ''''''

  SET @cnt = 1

  IF @db IS NOT NULL

  BEGIN

  CREATE TABLE #1 (

  fileid INT,

  filename SYSNAME,

  name SYSNAME)

  SET @cmd = 'Insert into #1 (fileid,filename,name) Select fileid,filename,name from '

  + QUOTENAME(@db)

  + '.dbo.sysfiles'

  EXEC( @cmd)

  SELECT @filecnt = MAX(fileid)

  FROM #1

  WHILE @cnt 0)

  ORDER BY dbid

  OPEN db_cursor

  FETCH NEXT FROM db_cursor

  INTO @db

  WHILE @@FETCH_STATUS = 0

  BEGIN

  CREATE TABLE #2 (

  fileid INT,

  filename SYSNAME,

  name SYSNAME)

  SET @cmd = 'Insert into #2 (fileid,filename,name) select file_id, physical_name, name

  from sys.master_files where database_id = db_id('''+@db+''')'

  EXEC( @cmd)

  SELECT @filecnt = (select count(*) from sys.master_files where database_id = db_id(@db))

  FROM #2

  WHILE @cnt <= @filecnt

  BEGIN

  SELECT @TempFileName = filename

  FROM #2

  WHERE fileid = @cnt

  SELECT @TempFileName = RTRIM(@TempFileName)

  SELECT @a = @a

  + ', '

  + CHAR(13)

  + CHAR(9)

  SELECT @a = @a

  + '@filename'

  + CONVERT(VARCHAR(2),@cnt)

  + ' = '

  + @sq

  + @TempFilename

  + @sq

  SET @cnt = @cnt

  + 1

  END

  SELECT @a = 'EXEC sp_attach_db @dbname = '

  + @sq

  + @db

  + @sq

  + @a

  PRINT @a

  PRINT 'GO'

  SELECT @a = ' '

  DROP TABLE #2

  SET @cnt = 1

  FETCH NEXT FROM db_cursor

  INTO @db

  END

  CLOSE db_cursor

  DEALLOCATE db_cursor

  END

  Note this will pull the Full Text index files in the attach statement as well.

  David

  @SQLTentmaker

  “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot