mssqltips logo

SQL Server Filegroup Backups



By:
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 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

Last Update: 3/12/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Thanks Greg. That helped a lot


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

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.

-Greg


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

 Dear Greg Please help me out


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

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>

thanks


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

Hi Amir,

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

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-files-and-filegroups-sql-server?view=sql-server-2017

-Greg


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

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

thanks


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

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.

-Greg


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

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. 

 



download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools