Partial Backup/Restore Filegroup Synchronization

Our current application which is deployed to numerous client sites usually requires the database to be returned to us to upgrade for the next release. The current process is:

- Perform a full backup at the client site
- Send the backup to us for upgrade
- Perform a full restore process to our local db server
- Modify the database
- Perform a full backup
- Send the backup to the client
- Perform a full restore over the original database.

This has served our purposes so far, however, we are about to introduce functionality that will enable the client to upload files for storing in the database.

To manage this we have a single DBFiles table which due to the potential size of it’s contents we determined that it would be best to store it in it’s own FileGroup. The idea being that then we could backup just the Primary FileGroup for returning to our offices as the DBFiles table would remain untouched.

The proposed process would now be:

- Perform a partial backup of the primary filegroup at the client site
- Perform a partial backup of the DBFiles filegroup at the client site. This only ever stays at the client site and is never restored at our office.
- Send the primary filegroup backup to our office for upgrade
- Perform a partial restore or the primary filegroup to our local db server. This will leave the DBFiles filegroup offline which is OK as we do not need to change it.
- Modify the database
- Perform a partial backup of the primary filegroup
- Send the partial backup to the client
- Perform a partial restore of the primary filegroup over the original database. This will leave the DBFiles filegroup offline.
- Perform a partial restore of the DBFiles filegroup over the original database.

This last step results in the recovery failing due to a reason like “The roll forward start point is now at log sequence number (LSN) 66787000000001800001. Additional roll forward past LSN 66787000000008900001 is required to complete the restore sequence�.

I have tried a number of variations including backing up both the client and local log files for recovery and using copy_only but I have yet to be able to successfully complete the process. Is there a means we can synchronize these 2 backup files without worrying about the logs for this part of the process?

Our requirements are basically to be able to perform a partial backup and exclude the DBFiles table as it is likely to be very large so we can send it back to our offices for upgrade and then return it to the client for restoring back to the original database while maintaining the data in the clients DBFiles filegroup and table.

Any guidance on what we need to change or a better process would be appreciated.