SQL Server – Restrict snapshot generation to only newly added article to SQL Server Replication

Problem:

I have Transactional Replication configured and when I add new article into the replication and generate new snapshot, it gets generated for all the articles.  As generating snapshot for all the articles is a waste of time and resources [Disk, CPU etc], I want to generate snapshot only for newly added articles.

Solution:

This is a common request to add a new article to a publication.  To save resources we will walk through how to only create the snapshot for the new article(s).

Assuming Transactional Replication is already configured.

 

The publication has 4 objects/tables to be published.

1

Now, we are adding a new object [BillOFMaterials(Production)] to the publication.  When we add new article to the publication, the publication becomes invalid and we need to create a new snapshot.

 

 

2

When we start the snapshot, it generates snapshot for all 5 articles in the publication.

 

 

 

3

 

 

To achieve what we want, we need to disable options [allow_anonymous and immediate_sync].

Run the below commands on your publisher database for your publication to make the changes.

4

 

 

–Run on your publisher database
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘allow_anonymous’ ,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘immediate_sync’ ,
@value = ‘false’
GO

 

Now, lets add another article [BusinessEntityAddress[Person] to the publication and generate a new snapshot.

 

5

 

This time it gets generated for only the newly added article.

 

6

 

7

 

 

Hope it was helpful !!!!