How to Add New Table(s) to Transactional Replication WITHOUT Re-Generate the Whole Snapshot

You have transactional replication configured in your production environment. You need to add a new article (table) to the publication. You wish to initialize only the new article added to the publication, in order to avoid taking a total snapshot of all existing articles in the publication. Below steps teach you how to do:

1. Set Publication Properties

USE <DB>

GO

DECLARE @publication sysname = '<publication>'

-- check immediate_sync

EXEC sp_helppublication @publication

-- If immediate_sync = 1, fix it (independent_agent must be 1) by disable immediate_sync

EXEC sp_changepublication

@publication = @publication,

@property = 'allow_anonymous', @value = 'False'

EXEC sp_changepublication

@publication = @publication,

@property = 'immediate_sync', @value = 'False'

--check

EXEC sp_helppublication @publication

2. Add New Table(s) to the Publication's Articles list

Right-click the Publication -> "Properties" -> "Articles" -> Uncheck "Show only checked articles in the list" -> Check the "New Table" in the list -> Press "OK", e.g.

3. Generate mini-Snapshot for the New Table(s)

Right-Click that "Publication" -> "View Snapshot Status" -> Press "Start".

It should only generate 1 article (if only one new table was added) as shown below: