2

I have a SQL Server 2005 database where the publication and subscriber schema are different. This was intentionally done by turning the "Replicate Schema" option to False.

However I now need to propagate the publication schema to the subscriber.

What is the best way of achieving this?

Improve this question

2 Answers 2

Reset to default
1

I would use Visual Studio's Schema Comparison tool, or some third party equivalent; it will visually show you what's different and optionally create a script to sync them. Just beware that depending on the change, it may try to copy the table to a work table and then drop the source and rename the new table, which will fail if the table is replicated.

Improve this answer
0

What is the best way of achieving this?

The best way is to script out replication and edit the scripts.

exec sp_addarticle ...
         ,@destination_table = N'Your_Subscriber_TableName'
         , @destination_owner = N'NewSchema'

Then run the snapshot agent and the log reader agent to sync data.

Improve this answer
2
  • Thanks for the comment, but I don't necessarily know what schema has been changed. I need to identify this first. Also any reason why script vs gui? I find the gui much more reliable than the generate scripts function for replication.
    – Hpk
    Jun 27, 2016 at 14:05
  • You have to find out by comparing Publisher and schema objects that are currently replicated. I prefer T-SQL as it has more options than GUI and is very useful if you have to automate it .
    – Kin Shah
    Jun 27, 2016 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.