What backup/restore strategy can I use for the following scenario

Question

We have two databases on a server which are being synced using back up and restore on a scheduled basis. Now, we would like to exclude a few tables getting affected by the back up and restore process.

What is the best way of doing this using backup and restore ?

I tried keeping the tables that should not be affected in a secondary file group and performing a file group back up and restore.

I ran into various issues during the restore process.

The following message was appeared while restoring the PRIMARY file group:

The roll forward start point is now at log sequence
number (LSN) 507000000318400006. Additional roll forward past LSN
507000000318400006 is required to complete the restore sequence.
Gone through some online content and tried restoring the last log backup and got the following     message:

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database.

And then I ran into the following article in sqlservercentral

http://www.sqlservercentral.com/blogs/dotnine-sql-server-and-more/2014/02/20/restoring-filegroup-backups-on-another-server-and-why-it-wont-work/

Question 1: How can I exclude few tables from syncing during a backup and restore process?

Question 2: According to article, I cannot achieve this using backup/restore. I that correct?

Thank you...

Answer 1

You cannot exclude parts of the database from backup and restore in the sense that those parts disappear or just become empty. SQL Server requires that all filegroups are either current or offline. You can bring non-current offline filegroups online by making them current by restoring enough backups.

A database is an inseparable entity when it comes to data and consistency.

If you could empty partitions/tables by not restoring them you could break trusted foreign keys and indexed views.

Answer 2

You generally can't restore just part of a database like that as it potentially leaves things in an inconsistent state.

If your groups of data are sufficiently loosely coupled that a partial restore is valid, then you could keep them in different databases - the SQL Server query planner/runner is capable of working with queries over multiple databases pretty well. If you want to avoid needing to use the longer .. syntax for referring to tables and views then you can create a view in the one database referring to the base table/view in the other (i.e. CREATE VIEW some_object AS SELECT * FROM other_db.schema.some_object). Calling stored procedures and using functions between databases is more problematical which may be a problem depending on your overall design, and you can't vary database names without using ad-hoc SQL so your database names become tightly coupled to your design, and foreign key relationships can not be enforced between DBs, so this method does have significant issues but it might solve your problem if these issues would not be show-stoppers for you.

Another two methods you could possibly use withoput hiving data off into separate databases are:

• Write your own synchronisation code that just updates the tables you intend to update, restoring the backup then copying the data over. This could be a fairly simple SSIS package or even just a single SQL script, but would be less efficient than the partial restore you are looking for.
• The same thing but the other way around: rename the database to be replaced, restore the new complete copy, then replace the tables from the old DB that you want to keep.

I'm guessing that the reason you want to perform a partial backup is to save the storage space, transmission time, and backup+restore time associated with the backup and those two options would not help you in that case - but if you have other reasons to want to do this then they might be option to consider.