Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to produce SP_ATTACH_DB code Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2007 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:39 AM
Points: 14, Visits: 404
Comments posted to this topic are about the item Script to produce SP_ATTACH_DB code


Post #405669
Posted Thursday, May 7, 2009 2:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:04 PM
Points: 2,107, Visits: 3,581
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 <= @filecnt
BEGIN
SELECT @TempFileName = filename
FROM #1
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
END
ELSE
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('tempdb','master','msdb','model')
AND dbid NOT IN (SELECT database_id
FROM sys.databases
WHERE state > 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
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #712442
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse