SQL Server Filegroup Backups

By: Greg Robidoux

Overview

In addition to doing "File" backups you can also do "Filegroup" backups which allows you to backup all files that are in a particular filegroup.  By default each database has a PRIMARY filegroup which is tied to the one data file that is created.  You have an option of creating additional filegroups and then placing new data files in any of the filegroups.  In most cases you will probably only have the PRIMARY filegroup, so this is topic is not relevant.

Explanation

As mentioned above you can back up each filegroup individually.  The one advantage of using filegroup backups over file backups is that you can create a Read-Only filegroup which means the data will not change.  So instead of backing up the entire database all of the time you can just backup the Read-Write filegroups.

A filegroup backup can be completed either using T-SQL or by using SSMS.

Create a SQL Server filegroup backup of the TestBackup database

For this example I created a new database called TestBackup that has three data files and one log file.  Two data files are the PRIMARY filegroup and one file is in the ReadOnly filegroup.  The code below shows how to do a filegroup backup.

T-SQL

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' 
TO DISK = 'C:\TestBackup_ReadOnly.FLG'
GO

SQL Server Management Studio

• Right click on the database name
• Select Tasks > Backup
• Select either "Full" or "Differential" as the backup type
• Select "Files and filegroups"
• Select the appropriate filegroup and click "OK"

• Select "Disk" as the destination
• Click on "Add..." to add a backup file and type "C:\TestBackup_ReadOnly.FLG" and click "OK"
• Click "OK" again to create the backup and repeat for other filegroups