I want to back up my DB. I have some huge tables in some specific FileGroups which I want to exclude them to be getting backup. I have tried this script:

   FILEGROUP = 'Temp_FG',
   FILEGROUP = 'Index_FG'
TO DISK = 'E:\TestBackup\Test.bak';  

But I am getting this error:

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.

What can I do to get the backup including only these 3 FileGroups or excluding the 'Dump_FG'?

  • 'Dump_FG' FileGroup is not ReadOnly.

That is just the way that SQL Server work. If you were able to exclude read-write filegroups in simple recovery, then you wouldn't be able to restore them. A restore need log backups to apply modifications to the data you just restored. Since you can't do log backup in simple mode, then those filegroups in your backup would be "doomed" - for even inaccessible (I believe that the state is "defunct" in SQL Server). And you cannot have a doomed primary filegroup. If you have data which is irrelevant for you,then consider truncating those table to get rid of the data or set them to read-only and exclude them.


Community wiki answer:

Think about (and test) your recovery plan.

If you could exclude a read-write filegroup from a partial backup in the SIMPLE model, you could never recover that filegroup beyond the last full or differential backup, nor restore from a later partial backup of other read-write filegroups. Otherwise, the database would be inconsistent.

  • I do not need those tables and they will be filled in future through gathering data from another servers. – A.Kazemi Jun 11 '18 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.