Partial Backup / Restore Sql Server Simple Recovery

Question

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:

1. Primary Data File - Move
2. FileGroup 1 - No Change - Don't Move
3. FileGroup 2 - No Change - Don't Move
4. 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.

Thanks,

S

Answer 1

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.