This article describes some very basic things about SQL Server filegroups.
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]
( NAME = N'FG_data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_data.mdf' ,
SIZE = 4096KB , FILEGROWTH = 1024KB )
( NAME = N'FG_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
ALTER DATABASE [FG] ADD FILEGROUP [FG2]
ALTER DATABASE [FG] ADD FILEGROUP [FG3]
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:
6. Creating a file and assigning it to filegroup:
select * from sys.filegroups
To add a file in a filegroup we execute ALTER DATABASE query.
7. Changing default filegroup:
ALTER DATABASE FG
(NAME = FG3_data,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FG3_data.ndf')
TO FILEGROUP FG3
Again ALTER DATABASE query will execute to change the default filegroup.
ALTER DATABASE FG
MODIFY FILEGROUP FG3 DEFAULT
Now check the default value for filegroup FG3 using sys.filegroups
select * from sys.filegroups
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:
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
Points to remember:
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:
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
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 :-)