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:
BACKUP DATABASE DB_NAME FILEGROUP = ‘PRIMARY’ TO DISK = ‘PATH:\DB_NAME.BAK”
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.
BACKUP DATABASE DB_NAME READ_WRITE_FILEGROUPS TO DISK = ‘PATH\DB_NAME.BAK’
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