Script to produce SP_ATTACH_DB code

  • russellgh

    SSC Enthusiast

    Points: 141

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

  • David Benoit

    SSC-Dedicated

    Points: 34562

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply