SQL SERVER – Create Multiple Filegroup For Single Database

I am elated to receive hundreds of emails every day from my readers. My tight work schedule refrains me from answering all your questions, but I do try my best to entertain them whenever I can. Today’s post revolves around a question I received a number of times last year but never blogged on it. On positive side, you are reading about that interesting subject today.

The question is – How to create multiple filegroup for any database?

To find solution to this query, we will go through the following four cases.

1) Creating New Database

a) Using T-SQL
b) Using SSMS

2) Updating Existing Database and Adding Filegroup

a) Using T-SQL
b) Using SSMS

Let us go over each of the cases one by one.

1) Creating New Database
a) Using T-SQL

CREATE DATABASE [FGTest] ON  PRIMARY
( NAME = N'FGTest',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [Secondary] ( NAME = N'FGTest_2',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FGTest_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO

b) Using SSMS

2) Updating Existing Database and Adding Filegroup

Updating existing database is much simpler compared with adding filegroup to new database. Let us take a look at how quickly it can be completed in a few simple steps.

a) Using T-SQL
We will add a Third filegroup to the same database where we added secondary filegroup in the above example.

USE [master] GO
ALTER DATABASE [FGTest] ADD FILEGROUP [Third] GO

b) Using SSMS
Right click on the database and go to properties and filegroup. Add new filegroup by simply adding name.

I encourage all my readers to keep mailing me send in your questions and doubts. I will certainly take them up at some point of time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.