I have a scenario where we are using SQL Server 2008 R2 with FileStream. I have partitioned the table where our files live into multiple filegroups. What I want to be able to do is backup our primary data file and then ONLY the filestream filegroups that have changed and move them to another server. I have a space limitation (files must fit on CD) so I only really want to move those fileGroups that have changed.
Server: Sql Server 2008 R2 Recovery: Simple
- Primary Data File - Move
- FileGroup 1 - No Change - Don't Move
- FileGroup 2 - No Change - Don't Move
- FileGroup 3 -Chagned - Move
My problem is that it appears that because my DB is in simple recovery I can't backup the individual filegroups I want to restore. I would have to make FileGroups 1 & 2 Read Only and then use READ_WRITE_FILEGROUPS which could make it so my .Bak file is Greater than the CD Size limitation.
So with Simple Recovery is there a way I can still backup Primary Data File and FileGroup 3 separately and restore them or do I have to use READ_WRITE_FILEGROUPS? If so sounds like I will have to change the recovery model on our server.