there are about 300 tables in our replication,  i added one table through GUI,(replication monitor), then start Snapshot agent, will this reinitialize  all the table or just one newly added table.

i am using SQL 2008 R2 publisher, distributor SQL Server 2012, after started snapshot, it started reinitialize , is it because of , added table through GUI instead of script

thanks

V

Hello - AFAIK, If you are using SQL Server 2008 or later version then adding an article will essentially generate full snapshot NOT incremental one.

If you just want to generate incremental snapshot then you need to tweak properties for this publication & these are immediate_sync & allow_anonymous:

EXEC sp_changepublication
@publication = 'PUB_NAME', --Enter your publication_name
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'PUB_NAME', --Enter your publication name
@property = 'immediate_sync' , 
@value = 'false'

Note: The above changes essentially invalidates the existing snapshot (Because it will only generate mini-snapshot now) and therefore you will have to generate full snapshot if you need to add more subscribers in future. However for the existing subscribers it will generate snapshot for the articles that are added just now

Hope this helps!

can you pl. details about

@property = 'allow_anonymous' , 

@property = 'immediate_sync' 

which will generate mini-snapshot?

• Marked as answer by Vaishu 48 minutes ago

Hello - If the Immediate_sync is enabled, every time you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone & to disable immediate_sync first allow_anonymous needs to set to false

Once you have set both properties to false, add your article and generate snapshot, you should see only incremental snapshot being generate instead of full 

More Information:

http://www.replicationanswers.com/transactional.asp

http://ansqldba.blogspot.in/2012/02/adding-new-article-to-existing.html

Hello - It appears that you have marked your own post as answer.

Would you mind marking my post as answered if you think that answered your query instead ?

can you pl. details about

@property = 'allow_anonymous' , 

@property = 'immediate_sync' 

which will generate mini-snapshot?

• Marked as answer by Vaishu Friday, June 19, 2015 6:19 AM

Here in the steps you need to follow:

https://gallery.technet.microsoft.com/Adding-Article-To-5ee2a705

You need to do the following from a new query window in the publication database

EXEC sp_changepublication @publication = N'PublicationName', @property = N'immediate_sync', @value = N'FALSE'
GO
EXEC sp_changepublication @publication = N'PublicationName', @property = N'allow_anonymous',@value=N'FALSE'
GO
exec sp_addarticle  PublicationName, TableName, @source_object =TableName
GO
exec sp_refreshsubscriptions PublicationName
GO
Then run your snapshot agent.