Partial Backup / Restore Sql Server Simple Recovery 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

Example:


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 &amp; 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. 

Thanks,

S
In short, no, you can't backup the individual files that you want to restore. According to BOL, the only way to backup files on a database that uses simple recovery model is if they are all backed up up together: http://msdn.microsoft.com/en-us/library/ms179401.aspx .
Have you considered differential backups of the files? Doing this would give you smaller file size for backups (still requires a full at some point), but would also increase the number of steps required to restore the database. Differentials partial backups are described here: http://msdn.microsoft.com/en-us/library/ms190218.aspx.