The Primary Filegroup Cannot be Backed Up as a File Backup

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

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

 

 

Share

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads