November 18, 2009 at 10:46 am
Im developing a procedure to copy a client database with tables and data altogether.
i bcp everything out as .csv files including the tables, procedures, functions and indexes schemas, rar everything up and mail it to target email
everything working as supposed to but i got one hickup, the CREATE DATABASE statement.
i want to create the database with the same file names and same filegroups of the client database.
the code im using is as follows
DECLARE @NOMEBANCO VARCHAR(256)
SET @NOMEBANCO = 'TESTDB'
EXEC('
CREATE TABLE #DATABASE (FILES VARCHAR(100), FILEGROUP VARCHAR(100))
--BULK INSERT #DATABASE
--FROM ''C:\BASE\DATABASE.csv''
--WITH (ROWTERMINATOR='':::'',FIELDTERMINATOR='';:;'')
--MANUALLY INSERTING
INSERT INTO #DATABASE
SELECT ''FILE1'',''PRIMARY'' UNION ALL
SELECT ''FILE2'',''SECONDARY'' UNION ALL
SELECT ''FILE3'',''THIRD'' UNION ALL
SELECT ''FILE4'',''FOURTH''
DECLARE @CRIABANCO VARCHAR(MAX)
SELECT DISTINCT @CRIABANCO = ''CREATE DATABASE '+@NOMEBANCO+' ON ''+ LEFT(RTRIM(ARQU),LEN(RTRIM(ARQU))-1) + '' LOG ON ( NAME = N''''Nectar_Log'''', FILENAME = N''''C:\'+@NOMEBANCO+'_LOG.ldf'''' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)''
FROM sys.tables ST
CROSS APPLY (SELECT '''' + CASE DB.FILEGROUP WHEN ''PRIMARY'' THEN DB.FILEGROUP ELSE ''FILEGROUP [''+DB.FILEGROUP+'']'' END + '' ( NAME = N'''''' + DB.FILES + '''''',FILENAME = N''''C:\'+@NOMEBANCO+'''+DB.FILES+''.mdf'''',SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), ''
FROM #DATABASE DB
FOR XML PATH('''')) DB (ARQU)
EXEC(@CRIABANCO)
')
so far so good, it works
the problem is if you have more than 1 file on the same filegroup, as in
TRUNCATE TABLE #DATABASE
INSERT INTO #DATABASE
SELECT 'FILE1','PRIMARY' UNION ALL
SELECT 'FILE2','SECONDARY' UNION ALL
SELECT 'FILE3','THIRD' UNION ALL
SELECT 'FILE4','FOURTH' UNION ALL
SELECT 'FILE5','FOURTH'
it just tried to create another filegroup with the same name, which is not what i want to.
so far i have no ideas how to accomplish this.
Any ideas are appreciated, thanks.
--
Thiago Dantas
@DantHimself
November 20, 2009 at 6:18 am
You are correct in saying that it has to do with the file groups. When the fourth FileGroup is created it is assigning the files at the same time. Since the Fourth Filegroup is already created, when adding the fifth file to the Fourth Group, an error occurs. Here's a script of before and after. Notice the Files are assigned at the same time the group is created
INCORRECT
CREATE DATABASE TESTDB ON PRIMARY
( NAME = N'FILE1', FILENAME = N'C:\TESTDBFILE1.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'FILE2',FILENAME = N'C:\TESTDBFILE2.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [THIRD]
( NAME = N'FILE3',FILENAME = N'C:\TESTDBFILE3.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [FOURTH]
( NAME = N'FILE4',FILENAME = N'C:\TESTDBFILE4.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [FOURTH]
( NAME = N'FILE5',FILENAME = N'C:\TESTDBFILE5.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'Nectar_Log', FILENAME = N'C:\TESTDB_LOG.ldf' ,
SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
MODIFIED
CREATE DATABASE TESTDB ON PRIMARY
( NAME = N'FILE1', FILENAME = N'C:\TESTDBFILE1.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'FILE2',FILENAME = N'C:\TESTDBFILE2.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [THIRD]
( NAME = N'FILE3',FILENAME = N'C:\TESTDBFILE3.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [FOURTH]
( NAME = N'FILE4',FILENAME = N'C:\TESTDBFILE4.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'FILE5',FILENAME = N'C:\TESTDBFILE5.mdf',
SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'Nectar_Log', FILENAME = N'C:\TESTDB_LOG.ldf' ,
SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 20, 2009 at 8:22 am
Yeah i know, i managed to find a solution. i split the #DATABASE table into a #FILEGROUP table and a #FILES table
CREATE TABLE #DATABASE (FILES VARCHAR(100), FILEGROUP VARCHAR(100))
CREATE TABLE #FILEGROUP (FILEGROUP VARCHAR(100),ID INT identity(1,1))
CREATE TABLE #FILES (FILES VARCHAR(100),FILEGROUP INT)
--BULK INSERT #DATABASE
--FROM 'C:\BASE\DATABASE.csv'
--WITH (ROWTERMINATOR=':::',FIELDTERMINATOR=';:;')
INSERT INTO #DATABASE
SELECT 'FILE1','PRIMARY' UNION ALL
SELECT 'FILE2','SECONDARY' UNION ALL
SELECT 'FILE3','THIRD' UNION ALL
SELECT 'FILE4','FOURTH' UNION ALL
SELECT 'FILE5','FOURTH'
DELETE FROM #DATABASE WHERE FILEGROUP = 'PRIMARY'
INSERT INTO #FILEGROUP
SELECT DISTINCT FILEGROUP
FROM #DATABASE
INSERT INTO #FILES
SELECT FILES, ID
FROM #DATABASE
INNER JOIN #FILEGROUP ON #DATABASE.FILEGROUP = #FILEGROUP.FILEGROUP
and the trick was to nest another CROSS APPLY inside the CROSS APPLY
SELECT DISTINCT 'CREATE DATABASE '+@NOMEBANCO+' ON PRIMARY ( NAME = N''Nectar_Data'',FILENAME = N''C:\'+@NOMEBANCO+'Nectar_Data.mdf'',SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), '+ LEFT(RTRIM(ARQU),LEN(RTRIM(ARQU))-1) + ' LOG ON ( NAME = N''Nectar_Log'', FILENAME = N''C:\'+@NOMEBANCO+'_LOG.ldf'' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)'
FROM sys.tables ST
CROSS APPLY (SELECT 'FILEGROUP [' + FG.FILEGROUP +']'+ NAME
FROM #FILEGROUP FG
CROSS APPLY (SELECT ' ( NAME = N''' + DB.FILES + ''',FILENAME = N''C:\'+@NOMEBANCO+''+DB.FILES+'.mdf'',SIZE = 107200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), '
FROM #FILES DB
WHERE FG.ID = DB.FILEGROUP
FOR XML PATH('')) FILES (NAME)
FOR XML PATH('')) DB (ARQU)
--
Thiago Dantas
@DantHimself
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply