Creating a filegroup

• Vinoo

  Say Hey Kid

  Points: 670

  More actions

  May 6, 2009 at 12:51 am

  #131216

  Works:

  ------

  ALTER DATABASE CCBS ADD FILEGROUP [1111]

  Does Not Work:

  ---------------

  declare @filegroupname varchar(10)

  set @filegroupname = '11111'

  ALTER DATABASE CCBS ADD FILEGROUP @filegroupname

  Question:

  As you can see, in the first scenario, I am having the name of the filegroup in the command itself and it works fine.

  But when I try to assign it to a variable and then use this variable in the 'alter database..' command, it fails by giving an error msg (Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@filegroupname'.)

  Is not possible to assign the name of the filegroup into a variable and use it in the 'alter database...' command???

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  May 6, 2009 at 2:27 am

  #989373

  Vinoo (5/6/2009)

  ---

  Works:

  ------

  ALTER DATABASE CCBS ADD FILEGROUP [1111]

  Does Not Work:

  ---------------

  declare @filegroupname varchar(10)

  set @filegroupname = '11111'

  ALTER DATABASE CCBS ADD FILEGROUP @filegroupname

  Question:

  As you can see, in the first scenario, I am having the name of the filegroup in the command itself and it works fine.

  But when I try to assign it to a variable and then use this variable in the 'alter database..' command, it fails by giving an error msg (Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@filegroupname'.)

  Is not possible to assign the name of the filegroup into a variable and use it in the 'alter database...' command???

  You can't use variables like that. You need to use dynamic sql.

  Something like this

  DECLARE @cmd nvarchar(2000), @filegroupname varchar(10)

  SET @filegroupname = '11111'

  SET @cmd = 'ALTER DATABASE CCBS ADD FILEGROUP ' + @filegroupname

  EXEC sp_executesql @cmd

  [font="Verdana"]Markus Bohse[/font]

• Johan Bijnens

  SSC Guru

  Points: 135510

  More actions

  May 6, 2009 at 4:05 am

  #989402

  As MarkusB showed, dynamic sql is the solution if you want to do it using TSQL.

  vbs or powershell using SQLSMO is an alternative.

  FYI I use this script to add a user filegroup and making it default (after db create) to keep primary clean for the catalog info.

  /*

  * alzdba dd 20070509

  * add filegroup FGUserData with one file and make it the DEFAULT filegroup

  * to split user-objects from system(catalog) objects to support filegroup-restores

  * so we can bring up the catalog asap

  * and then add the needed filegroup-restores

  *

  * sp_helpdb

  *

  */

  DECLARE @ExcludedDb bit

  /*

  * check excluded databases

  */

  IF EXISTS (SELECT *

  FROM master.sys.databases

  WHERE NAME not in ('master', 'msdb', 'tempdb', 'model' -- SQLServer system databases (The master and model databases cannot have files added to them)

  ,'Spotlight' ,'DDBADummy' ,'DJOBI' -- ALZDBA workdatabases

  ,'AdventureWorks', 'AdventureWorksDW', 'Northwind', 'Pubs' -- SQLServer sample databases

  ,'DDBAServerPing' -- ALZDBA pingdatabase

  )

  AND name = db_name()

  )

  -- Rij gevonden: alles OK

  SET @ExcludedDb = 0

  ELSE

  SET @ExcludedDb = 1

  /*

  * check if db has a non-PRIMARY filegroup

  */

  IF EXISTS (Select *

  FROM sys.filegroups

  WHERE NAME 'PRIMARY' )

  OR @ExcludedDb = 1

  BEGIN

  IF @ExcludedDb = 1

  BEGIN

  PRINT '*'

  PRINT '*'

  PRINT '- Database [' + db_name() + '] is an excluded database for this operation ! '

  PRINT '*'

  PRINT '*'

  END

  ELSE

  BEGIN

  PRINT '*'

  PRINT '*'

  PRINT '- Database already contains a non-PRIMARY filegroup'

  PRINT '*'

  PRINT '*'

  END

  END

  ELSE

  BEGIN

  DECLARE @Rc INT

  DECLARE @cmd NVARCHAR(MAX)

  DECLARE @InitialSizeKB VARCHAR(15)

  DECLARE @IncrementKB VARCHAR(15)

  DECLARE @Filename NVARCHAR(260)

  SET @Rc = -1

  SELECT TOP 1 @Filename = REPLACE (filename , '.mdf' , '_FGUserData.NDF')

  , @InitialSizeKB = CAST(([Size] * 8 ) AS VARCHAR(15))

  , @IncrementKB = CAST(CEILING ([growth] * 8 ) AS VARCHAR(15)) -- normaly we use growth in MB

  FROM sys.sysfiles

  WHERE LOWER(filename) LIKE '%.mdf'

  SELECT @cmd = 'Print ''* [' + db_name() + '] *'' ; '

  + CHAR(10) + 'Alter database [' + db_name() + '] '

  + CHAR(10) + ' ADD FILEGROUP FGUserData ;'

  + CHAR(10)

  + 'Alter database [' + db_name() + '] '

  + CHAR(10) + ' ADD FILE ( NAME = FUserData, FILENAME = ''' + @Filename + ''', '

  + CHAR(10) + ' SIZE = ' + @InitialSizeKB + 'KB, MAXSIZE = UNLIMITED , FILEGROWTH = ' + @IncrementKB + 'KB )'

  + char(10) + ' TO FILEGROUP FGUserData;'

  + CHAR(10) + 'Alter database [' + db_name() + '] '

  + CHAR(10) + ' MODIFY FILEGROUP FGUserData DEFAULT;'

  --PRINT @cmd

  --PRINT '*'

  --PRINT '*'

  --PRINT '*'

  EXEC @Rc = sp_executesql @stmt = @cmd

  IF @Rc = 0

  BEGIN

  PRINT '*'

  PRINT '* FILEGROUP FGUserData added to database [' + DB_NAME()+ '].'

  PRINT '*'

  END

  ELSE

  BEGIN

  PRINT '*'

  PRINT '- Failure for database [' + DB_NAME()+ '] rc = [' + cast(@Rc as varchar(15)) + ']'

  PRINT '*'

  END

  END

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me