SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The Primary Filegroup Cannot be Backed Up as a File Backup

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 http://technet.microsoft.com/en-us/library/ms190257(v=sql.105).aspx




Tim Radney - Database Professional

Tim Radney - Database Professional by Tim Radney I am a Sr DBA for a top 40 US bank. I live in the south eastern US. I have been working with database since 1999 but only full time for the past three years.


Leave a comment on the original post [timradney.com, opens in a new window]

Loading comments...