How to: Reinitialize a Subscription (Replication Transact-SQL Programming)

Individual subscriptions can be marked for reinitialization so that a new snapshot is applied during the next synchronization. Subscriptions can be reinitialized programmatically using replication stored procedures. The stored procedure that is used depends on the type of subscription (push or pull) and the type of publication to which the subscription belongs.

To reinitialize a pull subscription to a transactional publication

  1. At the Subscriber on the subscription database, execute sp_reinitpullsubscription (Transact-SQL). Specify @publisher, @publisher_db, and @publication. This marks the subscription for reinitialization the next time the Distribution Agent runs.

  2. (Optional) Start the Distribution Agent at the Subscriber to synchronize the subscription. For more information, see How to: Synchronize a Pull Subscription (Replication Programming).

To reinitialize a push subscription to a transactional publication

  1. At the Publisher, execute sp_reinitsubscription (Transact-SQL). Specify @publication, @subscriber, and @destination_db. This marks the subscription for reinitialization the next time the Distribution Agent runs.

  2. (Optional) Start the Distribution Agent at the Distributor to synchronize the subscription. For more information, see How to: Synchronize a Push Subscription (Replication Programming).

To reinitialize a pull subscription to a merge publication

  1. At the Subscriber on the subscription database, execute sp_reinitmergepullsubscription (Transact-SQL). Specify @publisher, @publisher_db, and @publication. To upload changes from the Subscriber before reinitialization occurs, specify a value of true for @upload_first. This marks the subscription for reinitialization the next time the Merge Agent runs.

    Important

    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

  2. (Optional) Start the Merge Agent at the Subscriber to synchronize the subscription. For more information, see How to: Synchronize a Pull Subscription (Replication Programming).

To reinitialize a push subscription to a merge publication

  1. At the Publisher, execute sp_reinitmergesubscription (Transact-SQL). Specify @publication, @subscriber, and @subscriber_db. To upload changes from the Subscriber before reinitialization occurs, specify a value of true for @upload_first. This marks the subscription for reinitialization the next time the Distribution Agent runs.

    Important

    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

  2. (Optional) Start the Merge Agent at the Distributor to synchronize the subscription. For more information, see How to: Synchronize a Push Subscription (Replication Programming).

To set the reinitialization policy when creating a new merge publication

  1. At the Publisher on the publication database, execute sp_addmergepublication, specifying one of the following values for @automatic_reinitialization_policy:

    • 1 - changes are uploaded from the Subscriber before a subscription is automatically reinitialized as required by a change to the publication.
    • 0 - changes at the Subscriber are discarded when a subscription is automatically reinitialized as required by a change to the publication.

    Important

    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

    For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To change the reinitialization policy for an existing merge publication

  1. At the Publisher on the publication database, execute sp_changemergepublication, specifying automatic_reinitialization_policy for @property and one of the following values for @value:

    • 1 - changes are uploaded from the Subscriber before a subscription is automatically reinitialized as required by a change to the publication.
    • 0 - changes at the Subscriber are discarded when a subscription is automatically reinitialized as required by a change to the publication.

    Important

    If you add, drop, or change a parameterized filter, pending changes at the Subscriber cannot be uploaded to the Publisher during reinitialization. If you want to upload pending changes, synchronize all subscriptions before changing the filter.

    For more information, see How to: View and Modify Publication Properties (Replication Transact-SQL Programming).

See Also

Tasks

How to: Reinitialize a Subscription (RMO Programming)

Other Resources

Reinitializing a Subscription

Help and Information

Getting SQL Server 2005 Assistance