How to generate the snapshot just for one article

  • I have a VLDB in my environment. We have transaction replication configured.

    Recently we added three tables in the publisher. What is the best way to generate the snapshot only for these three articles?

  • If my memory serves me right, when you add a new object (Article) to an existing publication, it will create a snapshot for just that new article. It will not recreate snapshot for the other article that is already synched and being replicated. (Unless the other articles are out of Synch)

    I do not have a set up to test it out now though.

    -Roy

  • Hey Roy,

    I also thought the same thing but when I run the snapshot this started creating the files even for the tables which already existed. I just executed the snapshot agent job and nothing else. This was frustrating.

  • Please refer this link.

    http://www.eggheadcafe.com/software/aspnet/28024744/spaddarticle-without-recreating--entire-new-snapshot.aspx

    This article might have the answer. I didn't read it fully though.

    Please revert back to forum if you are able to get the answer for this post.

    M&M

  • luckysql.kinda (2/18/2011)


    Hey Roy,

    I also thought the same thing but when I run the snapshot this started creating the files even for the tables which already existed. I just executed the snapshot agent job and nothing else. This was frustrating.

    Run the following, before generating the new snapshot. I have used this many times. It works.

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'allow_anonymous',

    @value = 'false'

    GO

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'immediate_sync',

    @value = 'false'

    GO

  • Hi Guys , Let me clear your doubt here :

    If you are working on SQL 2000 , then you ca use the following command to generate only newely added article snapshot. And I recommend it that you should set it on sql 2000 :

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'allow_anonymous',

    @value = 'false'

    GO

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'immediate_sync',

    @value = 'false'

    GO

    Note : In SQL 2005 , this feature is bydefault. If you are working SQL 2005 , then no need to do anything . By default only newely added article snapshot will be generate.

    Ashok

    MCP|MCTS|MCITP|MBA IT|

  • ashok_a2004 (3/1/2011)


    Note : In SQL 2005 , this feature is bydefault. If you are working SQL 2005 , then no need to do anything . By default only newely added article snapshot will be generate.

    Ashok

    MCP|MCTS|MCITP|MBA IT|

    Hi Ashok,

    Are you 100% sure about this?

    I just checked this in SQL 2008. It generated snapshot of new as well as old articles.

    Suresh

  • We do this almost on a day to day basis.

    1. Add the article using script

    2 Add the subscription to the article

    3. Generate snapshot (1 article will be generated)

    Step 1 will only add the article to the publication, it will NOT force a subscriber to subscribe to it. Step 2 will subscribe the article.

    You may have issues if you edit the publication through the GUI as it tends to do things in its "own way" and not the best way. We have a rule here, "Dont use the GUI except to generate scripts which can be validated".

    exec sp_addarticle

    @publication = N'', @article = N'',

    @source_owner = N'', @source_object = N'',

    @destination_table = N'',

    @type = N'logbased', @creation_script = null, @description = null,

    @pre_creation_cmd = N'drop',

    @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',

    @ins_cmd = N'CALL procname',

    @del_cmd = N'CALL procname',

    @upd_cmd = N'MCALL procname',

    @filter = null, @sync_object = null

    GO

    exec sp_addsubscription @publication = N'',

    @subscriber = N'',

    @destination_db = N'',

    @subscription_type = N'push',

    @sync_type = N'automatic',

    @article = N'all',

    @update_mode = N'read only',

    @subscriber_type = 0

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply