October 2, 2007 at 8:40 am
Comments posted to this topic are about the item Script to produce SP_ATTACH_DB code
May 7, 2009 at 2:45 pm
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)
+ ', '
+ CHAR(13)
+ CHAR(9)
+ '@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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply