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 & 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
Best Answer
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.