SQL Server Filegroup Backups


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.


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.


TO DISK = 'C:\TestBackup_ReadOnly.FLG'

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 filegroup to backup
  • 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
filegroup backup in ssms

Comments For This Article

Tuesday, March 12, 2019 - 10:21:57 AM - Amir Back To Top (79270)

Thanks Greg. That helped a lot

Tuesday, March 12, 2019 - 9:07:44 AM - Greg Robidoux Back To Top (79268)

Amir, I don't think there is a way to delete a filegroup from a database/filesystem and then restore it at a later date.  I would suggest you archive the older data to an archive database and then bring that database back online in the future.  You could then use table partitioning and switching partitions in and out if needed or just query the older tables.  You could also create a view that combines the different tables to make it look like one large table.  Not sure what your needs are if you need to bring an older data back online.


Tuesday, March 12, 2019 - 4:17:42 AM - Amir Back To Top (79264)

 Dear Greg Please help me out

Monday, March 11, 2019 - 11:15:38 AM - Amir Back To Top (79254)

the story is that we have an Archive Database that we don't need all the data in it. We need to partition it and keep the 3 latest partitions in the Database and Backup the rest files to the tape and delete them from the main database. we need to have the ability to restore the deleted files in case if we needed them. I saw the website you referred to. that was not my answer. so plz keep helping me if you can. /p>


Monday, March 11, 2019 - 10:59:21 AM - Greg Robidoux Back To Top (79253)

Hi Amir,

if you have a filegroup backup you can follow the following to restore the filegroup:



Monday, March 11, 2019 - 10:39:13 AM - Amir Back To Top (79252)

 hi. how can i restore just a backup of a deleted file group.


Monday, February 15, 2016 - 10:26:34 AM - Greg Robidoux Back To Top (40683)

Hi Sachin,

A readonly filegroup is helpful if you have data that does change and you want to prevent updates to the data.  Since the data in the readonly filegroup does not change you can also change your backup processing to not have to continue to backup the data that doesn't change.  It does make your backup and restore process more complex though, so keep that in mind.


Monday, February 15, 2016 - 2:14:28 AM - Sachin Jain Back To Top (40681)

Dear Author,

If we create a filegroup and make it read-only, so whether we will not be able to modify(update/delete/insert) the data after that present in that filegroup?

I am still confused on the read-only filegroup and the advantage which comes with it.

Please help. Thanks in advance. 


get free sql tips
agree to terms