In this blog post, I will list down the steps on how to re-initialize just a single article in transaction replication
1. Turn off @allow_anonymous and @immediate_sync on the publication.
use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'false'
GO
The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.
2. Add new article.
EXEC sp_addarticle
@publication = 'Replication_Test',
@article = 'REPL_TABLE',
@source_object = 'REPL_TABLE',
@force_invalidate_snapshot = 1
3. Refresh the subscription
EXEC sp_refreshsubscriptions @publication = 'Replication_Test'
GO
4. Check the current snapshot agent history.
use distribution
go
select * from dbo.MSsnapshot_history
5. Start Snapshot agent.
EXEC sp_startpublication_snapshot @publication = 'Replication_Test';
GO
6. Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.
7. Turn ON @allow_anonymous and @immediate_sync on the publication.
use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'true'
GO
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'true'
GO
This is how you can re-initialize only the required articles.
