Types of Filegroups
There are two types of SQL Server filegroups: primary and user-defined.
Primary filegroup includes all pages for system tables, primary data file and all other files that are not part of any other filegroup.
A filegroup that is created using FILEGROUP keyword in CREATE DATABASE or ALTER DATABASE statement belongs to user-defined filegroup.
Assume there is a database ‘Test’ that has one primary filegroup ‘Group’ stored in Group.mdf and two user-defined filegroups ‘Group1’ and ‘Group2’ stored in Group1.ndf and Group2.ndf respectively. The transaction log is stored in Test.ldf file.
Each database has a default filegroup. User can specify any filegroup by default in which all new tables and indexes will be located. If the filegroup is not specified then the primary filegroup will be taken by default.
Each database file can be a part of only one filegroup. If tables and indexes are large, they will be associated with a specified filegroup and will be located there or will be partitioned. That means that the data of partitioned tables and indexes will be divided into units and will be stored in separate filegroups.
To add a new filegroup to a database use the following command:
ALTER DATABASE Test ADD FILEGROUP Group1; GO ALTER DATABASE Test ADD FILE (NAME = 'DataFile', FILENAME = 'C:\DataFile.ndf', SIZE = 2GB, FILEGROWTH = 10GB) TO FILEGROUP Group1;
How to Backup Database Filegroup
Only certain filegroups can be backed up. There are two common ways of doing it:
Using Transact-SQL to backup a filegroup:
BACKUP DATABASE Test FILEGROUP = 'Group1' TO DISK = 'C:\Group1.FLG'
The second way to backup database filegroup is to use SQL Server Management Studio (SSMS):
- Right click on the database where the filegroup you want to backup is located
- Select “Tasks”, then “Back Up…”
- Select backup type (“Full” or “Differential”)
- Select “Files and filegroups”
- Choose filegroup and click “OK”
- Add backup destination
- Click “OK”
