Creation of FileGroups in SQLServer 2005

We have to have various file groups for the database if we need to place the partitioned tables on different filegroups.

We can create filegroups in SQL Server by two different ways, one is Alter Command and the second is using Interface.

a) Using the Alter Command

The syntax for Alter Command is,

ALTER DATABASE ADD FILEGROUP

For Example,

Alter Database TestDB ADD FILEGROUP FG1

After adding a filegroup we need to add files to the filegroup.

The Syntax for adding files,

ALTER DATABASE
ADD FILE
(
NAME = , FILENAME = , SIZE = , MAXSIZE = , FILEGROWTH =
)
TO FILEGROUP ;

For Example,

ALTER DATABASE TestDB
ADD FILE
(
NAME = FILE1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\FILE1.ndf', SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 1MB
)
TO FILEGROUP FG1;

b) Using the Interface

We can create the filegroup in SQLServer 2005 using Interface by following steps,

1. Select the DB and right click -> Properties
2. Select the Filegroup section and add the necessary details and click the add button.
3. Next we will click on the Files section and add a new file and associate this file to the created filegroup
4. Then finally click on the OK button.


Using these two different ways, we can create filegroups in SQLServer.