Using SQL Server merge replication, one of the tables was accidentally unselected (check box unticked). How do I add it back in to the snapshot again? It was unfortunately a large table with plenty of child relationships.
There are 3 databases A - the publisher, B and C, the subscribers. There are a few rows that have been added on each, not many. I want to save all of them.
If I check the table again in the snapshot on server A and run it, then I get this error on server B:
Cannot drop table dbo.TableName because it is referenced by a foreign key constraint
I didn't want to drop it and reload it from scratch, I wanted to merge of course. Drop would lose the few rows that have been entered on B. And drop would affect the child tables too.
I understand that it is doing this because it thinks that the table is a new one in the snapshot, and the other server (B) already has a table with the same name, so it needs to be dropped first. I want it to merge instead.
So I changed the setting
Action if name is in use to
Keep existing object unchanged
But now it is trying to copy all rows from A to B, not just the changed ones so it errors with this on server B:
Violation of PRIMARY KEY constraint 'PK__TableName__id'.
How do I get out of this situation?
The @@version is
Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64)