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


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.


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.


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.




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







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.




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


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




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







Hope it was helpful !!!!



Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: