SQL Server Storage Basics: Database Filegroups

Note: All screenshots apply to SQL Server 2012 Enterprise Evaluation Edition.
 
1. What is Filegroup:
In SQL Server a filegroup is a logical structure that contains objects like data files, tables and indexes. In other words we can say that a filegroup is a logical unit in which all database files are grouped together and simplifies database administration resulting into improved performance by controlling the placement of objects into specific filegroups on a specific drive.
    
   
2. Why Filegroups:
Filegroups make administration easier for a DBA. Using multiple filegroups we can gain the following benefits:
1. We can separate user data with internal system data using multiple filegroups.
2. We can reduce maintenance overhead by putting archive (or even read-only) data onto their own filegroups and dedicated set of disks.
3. We can gain performance improvement by putting larger tables/indexes in their own filegroup and/or dedicated set of disks.
4. We can bring some parts of the database online quickly (piecemeal restore).

3. Types of Filegroups:
There are two types of filegroups:
3.1. Primary Filegroup
3.2. User defined/Secondary Filegroup
 
3.1. Primary Filegroup:
The filegroup that contains the primary data file and any other files that are not associated with another filegroup is termed as Primary filegroup.
 
3.2. User-defined Filegroup
The Filegroups that we create from FILEGROUP keyword using CREATE DATABASE or ALTER DATABASE are termed user-defined filegroups. These files are created by a user or are later modifications to the database by a user.
 
4. Filegroup Examples:
The query below creates a filegroup with new database:

CREATE DATABASE [FG]
                       ON  PRIMARY 
                      ( NAME = N'FG_data', 
                      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_data.mdf' , 
                      SIZE = 4096KB , FILEGROWTH = 1024KB )
                       LOG ON 
                      ( NAME = N'FG_log', 
                      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_log.ldf' , 
                      SIZE = 1024KB , FILEGROWTH = 10%)
                      GO
                      ALTER DATABASE [FG] ADD FILEGROUP [FG2]
                      GO
                      
                      ALTER DATABASE [FG] ADD FILEGROUP [FG3]
                      GO
                      

Select all Open in new window

Using above query we created database named ‘FG’, the primary filegroup (which is the default) and FG2 and FG3, two user-defined filegroups.
 
5. How to view filegroups:
By executing below query we can view filegroups in a database:

use FG
                      go
                      select * from sys.filegroups
                      go
                      

Select all Open in new window

 
6. Creating a file and assigning it to filegroup:
To add a file in a filegroup we execute ALTER DATABASE query.

ALTER DATABASE FG 
                      ADD FILE 
                      (NAME = FG3_data, 
                       FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG3_data.ndf') 
                      TO FILEGROUP FG3
                      

Select all Open in new window

 
7. Changing default filegroup:
Again ALTER DATABASE query will execute to change the default filegroup.

ALTER DATABASE FG
                      MODIFY FILEGROUP FG3 DEFAULT
                      

Select all Open in new window

Now check the default value for filegroup FG3 using sys.filegroups

use FG
                      go
                      select * from sys.filegroups
                      go
                      

Select all Open in new window

Now you can clearly see that the is_default value is 1 for FG3 filegroup. It means the default filegroup is FG3.

8. Filegroups Backup:
We can back up filegroups in two ways:
 
8.1. With SQL Server Management Studio (SSMS)
8.2. With T-SQL
 
8.1. With SQL Server Management Studio (SSMS):
To make a backup of filegroups with ssms follow these steps:
1) select database >> do right click
2) go to task >> click on backup
3) backup database window will appear. Choose option file and filegroups under backup component
4) when you select file and filegroups another window will open which will show you all the filegroups for that database.
5) click on check boxes to take backup of one or more filegroups according to your requirement.
 
Step1 and step2:

 
Step3:

Step4 and step5:

8.2. Filegroups backup with T-SQL:
You can execute below query to make backups of filegroups. In the example below my database name is ‘FG’ and I am making a backup of all 3 filegroups i.e. PRIMARY, FG2, FG3

BACKUP DATABASE [FG] 
                      FILEGROUP = N'PRIMARY',  
                      FILEGROUP = N'FG2',  
                      FILEGROUP = N'FG3' TO  
                      DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\FG.bak' 
                      WITH NOFORMAT, NOINIT,  NAME = N'FG-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO
                      

Select all Open in new window

 
Points to remember:

 
9. References:
[]1] Database file and filegroups. Technet Microsoft
[]2] Files and Filegroups Architecture. Technet Microsoft
[]3] Backup File and Filegroups. Technet Microsoft
[]4] Journey to SQL Authority with Pinal Dave
[]5] Microsoft SQL Server 2012 Internals By Kalen Delaney, Craig Freeman
 
10. Final Words:
In this section we discussed some basic things and saw some examples about how to create and use filegroups, which is closely related with article SQL Server Storage Basics: Database Files.

Stay blessed :-)