Stored Procedure schema changes are not being replicated when using ”initialize with backup”

SQL Replication allows you to replicate user-defined Stored Procedures code to subscribers similar to replicating tables. When the sp is modified, the new schema/script is transferred to the subscriber. This ensures all subscribers have the most recent version of the SP.


Transactional Replication also allows you to setup a subscriber using a backup of the Publisher. This option is called ”initialize with backup”. We encountered a problem in SQL 2005/2008 replicating a stored procedure schema change if the subscriber was initialized using the ”initialize with backup” feature. The Log Reader would not distributed the SP schema change to the Distribution database and therefore not update the subscribers. We’re still investigating the root cause but wanted to provide these ways to avoid the problem.

sp_addsubscription . . . @sync_type = N'initialize with backup',

Solutions
-------------------
1.a) Create 1 subscriber (even if on a same publisher) using Snapshot Replication

1.b) Drop the "snapshot" subscriber, the SP changes are correctly replicated.

2) Create ALL subscribers using Replication Snapshot or Backup/Restore @sync_type = N'replication support only'.

3) Don’t Replicate user-defined SP schema modifications. Instead using Scripting to SSIS packages to move the modified SP code.