Printed 2015/11/26 08:02AM

The Primary Filegroup Cannot be Backed Up as a File Backup

By Tim Radney, 2014/03/31

File Group backups are great when working with very large databases (VLDB’s) that have been partitioned.  Typically when I come across File Group backups implemented in production the database is using the full recovery model. With the full recovery model you can specify individual file groups for backup. A very common backup strategy for VLDB’s is to partition the non-changing data to secondary file groups so that only current data is being written to the primary file group.  This allows for a more efficient backup solution as well as recovery plan.  Performing file group backups allow for a piece meal restore meaning you can bring data back online in order of importance.  Backing up an individual file group can be done using syntax much like:


This TSQL statement will work if the database is utilizing the full recovery model, but what if the recovery model is in simple? In that case specifying FILEGROUP = ‘PRIMARY’ would not work.  You would get a message that states

Msg 3004, Level 16, State 1, Line 19
The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

How would you backup the primary file group?

In this case you would need to specify ‘READ_WRITE_FILEGROUPS’ in the statement.


If your secondary file groups are not read-only then they would also be backed up. If you are not writing data to those secondary file groups then you should consider marking them read-only.  There are added benefits to them being read-only in addition to this backup methodology.  You can read more about that here




Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.