How To Add File Group and New File to an Existing Database

In this post I am going to explain following questions:
1. How to add a File Group to a Database.
2. How to remove a File Group from a Database
3. How to add a File to an existing File Group.
4. How to remove a File from a File Group.

How to add a File Group to a Database
I am assuming that I have a Test database with default File Group (Primary). Now I want to add one more File Group with Secondary name. We can use following command to add new File Group:

USE MASTER
GO
IF NOT EXISTS(
SELECT name FROM Test.sys.filegroups
WHERE name = 'Seconday')
BEGIN
PRINT 'Adding [Seconday] FileGroup...'
ALTER DATABASE TestADD FileGroup Seconday
END


How to remove a File Group from a Database
You can use following command to drop existing File Group:

USE MASTER
GO
IF EXISTS(
SELECT name FROM Test.sys.filegroups
WHERE name = 'Seconday'
)
BEGIN
PRINT 'Dropping [Seconday] FileGroup...'
ALTER DATABASE Test
REMOVE FileGroup Seconday
END

How to add a File to an existing File Group
Lets assume that we want to add Data1 file (.ndf) to Secondary file group which we created in step1. We can use following command add new file:

IF NOT EXISTS(
SELECT name FROM Test.sys.database_files
WHERE name = 'Data1'
)
BEGIN
PRINT 'Adding [Data1] file to [Seconday] File Group...'
ALTER DATABASE Test
ADD FILE
(
NAME = Data1,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test1.ndf',
SIZE = 200MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5%
)
TO FILEGROUP Seconday
END
GO


How to remove a File from a File Group
We can use following command to remove the data file [data1] from [Secondary] file group  using following command:

IF EXISTS(
SELECT * FROM Test.sys.database_files
WHERE name = 'Data1'
)
BEGIN
PRINT 'Removing [Data1] file from [Seconday] File Group...'
ALTER DATABASE Test
REMOVE File Data1
END