This article describes some very basic things about SQL Server 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:
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
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 = 10GO
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.