Reinitialize a Subscription

Applies to: SQL Server Azure SQL Database

This topic describes how to reinitialize a subscription in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO). Individual subscriptions can be marked for reinitialization so that a new snapshot is applied during the next synchronization.

Note

Azure SQL Managed Instance can be a publisher, distributor, and subscriber for snapshot and transactional replication. Databases in Azure SQL Database can only be push subscribers for snapshot and transactional replication. For more information, see Transactional replication with Azure SQL Database and Azure SQL Managed Instance.

Using SQL Server Management Studio

Reinitializing a subscription is a two-part process:

1. A single subscription or all subscriptions to a publication are marked for reinitialization. Mark subscriptions for reinitialization in the Reinitialize Subscription(s) dialog box, which is available from the Local Publications folder and the Local Subscriptions folder in Microsoft SQL Server Management Studio. You can also mark subscriptions from the All Subscriptions tab and the publications node in Replication Monitor. For information about starting Replication Monitor, see Start the Replication Monitor. When you mark a subscription for reinitialization, you have the following options:

   Use the current snapshot
   Select to apply the current snapshot to the Subscriber the next time the Distribution Agent or Merge Agent runs. If there is no valid snapshot available, this option cannot be selected.

   Use a new snapshot
   Select to reinitialize the subscription with a new snapshot. The snapshot can be applied to the Subscriber only after it has been generated by the Snapshot Agent. If the Snapshot Agent is set to run on a schedule, the subscription is not reinitialized until after the next scheduled Snapshot Agent run. Select Generate the new snapshot now to start the Snapshot Agent immediately.

   Upload unsynchronized changes before reinitialization
   Merge replication only. Select to upload any pending changes from the subscription database before the data at the Subscriber is overwritten with a snapshot.

   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. A subscription is reinitialized the next time it is synchronized: the Distribution Agent (for transactional replication) or Merge Agent (for merge replication) applies the most recent snapshot to each Subscriber that has a subscription marked for reinitialization. For more information about synchronizing subscriptions, see Synchronize a Push Subscription and Synchronize a Pull Subscription.

To mark a single push or pull subscription for reinitialization in Management Studio (at the Publisher)

1. Connect to the Publisher in Management Studio, and then expand the server node.

2. Expand the Replication folder, and then expand the Local Publications folder.

3. Expand the publication that has the subscription you want to reinitialize.

4. Right-click the subscription, and then click Reinitialize.

5. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

To mark a single pull subscription for reinitialization in Management Studio (at the Subscriber)

1. Connect to the Subscriber in Management Studio, and then expand the server node.

2. Expand the Replication folder, and then expand the Local Subscriptions folder.

3. Right-click the subscription, and then click Reinitialize.

4. In the confirmation dialog box that is displayed, click Yes.

To mark all subscriptions for reinitialization in Management Studio

1. Connect to the Publisher in Management Studio, and then expand the server node.

2. Expand the Replication folder, and then expand the Local Publications folder.

3. Right-click the publication with subscriptions you want to reinitialize, and then click Reinitialize All Subscriptions.

4. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

To mark a single push or pull subscription for reinitialization in Replication Monitor

1. In Replication Monitor, expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

2. Click the All Subscriptions tab.

3. Right-click the subscription you want to reinitialize, and then click Reinitialize Subscription.

4. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

To mark all subscriptions for reinitialization in Replication Monitor

1. In Replication Monitor, expand a Publisher group in the left pane, and then expand a Publisher.

2. Right-click the publication with subscriptions you want to reinitialize, and then click Reinitialize All Subscriptions.

3. In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.

Using Transact-SQL

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 Synchronize a Pull Subscription.

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 Synchronize a Push Subscription.

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 Synchronize a Pull Subscription.

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 Synchronize a Push Subscription.

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 Create a Publication.

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 View and Modify Publication Properties.

Using Replication Management Objects (RMO)

Individual subscriptions can be marked for reinitialization so that during the next synchronization, a new snapshot is applied. Subscriptions can be reinitialized programmatically by using Replication Management Objects (RMO). The classes you use depend on the type of publication to which the subscription belongs and the type of subscription (that is, a push or pull subscription).

To reinitialize a pull subscription to a transactional publication

1. Create a connection to the Subscriber by using the ServerConnection class.

2. Create an instance of the TransPullSubscription class, and set PublicationName, DatabaseName, PublisherName, PublicationDBName, and the connection from step 1 for ConnectionContext.

3. Call the LoadProperties method to get the properties of the object.

   Note

   If this method returns false, either the subscription properties in step 2 were defined incorrectly or the pull subscription does not exist.

4. Call the Reinitialize method. This method marks the subscription for reinitialization.

5. Synchronize the pull subscription. For more information, see Synchronize a Pull Subscription.

To reinitialize a push subscription to a transactional publication

1. Create a connection to the Publisher by using the ServerConnection class.

2. Create an instance of the TransSubscription class, and set PublicationName, DatabaseName, SubscriberName, SubscriptionDBName, and the connection from step 1 for ConnectionContext.

3. Call the LoadProperties method to get the properties of the object.

   Note

   If this method returns false, either the subscription properties in step 2 were defined incorrectly or the push subscription does not exist.

4. Call the Reinitialize method. This method marks the subscription for reinitialization.

5. Synchronize the push subscription. For more information, see Synchronize a Push Subscription.

To reinitialize a pull subscription to a merge publication

1. Create a connection to the Subscriber by using the ServerConnection class.

2. Create an instance of the MergePullSubscription class, and set PublicationName, DatabaseName, PublisherName, PublicationDBName, and the connection from step 1 for ConnectionContext.

3. Call the LoadProperties method to get the properties of the object.

   Note

   If this method returns false, either the subscription properties in step 2 were defined incorrectly or the pull subscription does not exist.

4. Call the Reinitialize method. Pass a value of true to upload changes at the Subscriber before reinitialization or a value of false to reinitialize and lose any pending changes at the Subscriber. This method marks the subscription for reinitialization.

   Note

   Changes cannot be uploaded if the subscription is expired. For more information, see Set the Expiration Period for Subscriptions.

5. Synchronize the pull subscription. For more information, see Synchronize a Pull Subscription.

To reinitialize a push subscription to a merge publication

1. Create a connection to the Publisher by using the ServerConnection class.

2. Create an instance of the MergeSubscription class, and set PublicationName, DatabaseName, SubscriberName, SubscriptionDBName, and the connection from step 1 for ConnectionContext.

3. Call the LoadProperties method to get the properties of the object.

   Note

   If this method returns false, either the subscription properties in step 2 were defined incorrectly or the push subscription does not exist.

4. Call the Reinitialize method. Pass a value of true to upload changes at the Subscriber before reinitialization or a value of false to reinitialize and lose any pending changes at the Subscriber. This method marks the subscription for reinitialization.

   Note

   Changes cannot be uploaded if the subscription is expired. For more information, see Set the Expiration Period for Subscriptions.

5. Synchronize the push subscription. For more information, see Synchronize a Push Subscription.

Examples (RMO)

This example reinitializes a pull subscription to a transactional publication.

// Define server, publication, and database names.
String subscriberName = subscriberInstance;
String publisherName = publisherInstance;
String publicationName = "AdvWorksProductTran";
String publicationDbName = "AdventureWorks2022";
String subscriptionDbName = "AdventureWorks2022Replica";

// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(subscriberName);

TransPullSubscription subscription;

try
{
    // Connect to the Subscriber.
    conn.Connect();

    // Define subscription properties.
    subscription = new TransPullSubscription();
    subscription.ConnectionContext = conn;
    subscription.DatabaseName = subscriptionDbName;
    subscription.PublisherName = publisherName;
    subscription.PublicationDBName = publicationDbName;
    subscription.PublicationName = publicationName;

    // If the pull subscription and the job exists, mark the subscription
    // for reinitialization and start the agent job.
    if (subscription.LoadProperties() && subscription.AgentJobId != null)
    {
        subscription.Reinitialize();
        subscription.SynchronizeWithJob();
    }
    else
    {
        // Do something here if the subscription does not exist.
        throw new ApplicationException(String.Format(
            "A subscription to '{0}' does not exists on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Do appropriate error handling here.
    throw new ApplicationException("The subscription could not be reinitialized.", ex);
}
finally
{
    conn.Disconnect();
}

' Define server, publication, and database names.
Dim subscriberName As String = subscriberInstance
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2022"
Dim subscriptionDbName As String = "AdventureWorks2022Replica"

' Create a connection to the Subscriber.
Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As TransPullSubscription

Try
    ' Connect to the Subscriber.
    conn.Connect()

    ' Define subscription properties.
    subscription = New TransPullSubscription()
    subscription.ConnectionContext = conn
    subscription.DatabaseName = subscriptionDbName
    subscription.PublisherName = publisherName
    subscription.PublicationDBName = publicationDbName
    subscription.PublicationName = publicationName

    ' If the pull subscription and the job exists, mark the subscription
    ' for reinitialization and start the agent job.
    If subscription.LoadProperties() And (Not subscription.AgentJobId Is Nothing) Then
        subscription.Reinitialize()
        subscription.SynchronizeWithJob()
    Else
        ' Do something here if the subscription does not exist.
        Throw New ApplicationException(String.Format( _
         "A subscription to '{0}' does not exists on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Do appropriate error handling here.
    Throw New ApplicationException("The subscription could not be reinitialized.", ex)
Finally
    conn.Disconnect()
End Try

This example reinitializes a pull subscription to a merge publication after first uploading pending changes at the Subscriber.

// Define server, publication, and database names.
String subscriberName = subscriberInstance;
String publisherName = publisherInstance;
String publicationName = "AdvWorksSalesOrdersMerge";
String publicationDbName = "AdventureWorks2022";
String subscriptionDbName = "AdventureWorks2022Replica";

// Create a connection to the Subscriber.
ServerConnection conn = new ServerConnection(subscriberName);

MergePullSubscription subscription;

try
{
    // Connect to the Subscriber.
    conn.Connect();

    // Define subscription properties.
    subscription = new MergePullSubscription();
    subscription.ConnectionContext = conn;
    subscription.DatabaseName = subscriptionDbName;
    subscription.PublisherName = publisherName;
    subscription.PublicationDBName = publicationDbName;
    subscription.PublicationName = publicationName;

    // If the pull subscription and the job exists, mark the subscription
    // for reinitialization after upload and start the agent job.
    if (subscription.LoadProperties() && subscription.AgentJobId != null)
    {
        subscription.Reinitialize(true);
        subscription.SynchronizeWithJob();
    }
    else
    {
        // Do something here if the subscription does not exist.
        throw new ApplicationException(String.Format(
            "A subscription to '{0}' does not exists on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Do appropriate error handling here.
    throw new ApplicationException("The subscription could not be synchronized.", ex);
}
finally
{
    conn.Disconnect();
}

' Define server, publication, and database names.
Dim subscriberName As String = subscriberInstance
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2022"
Dim subscriptionDbName As String = "AdventureWorks2022Replica"

' Create a connection to the Subscriber.
Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As MergePullSubscription

Try
    ' Connect to the Subscriber.
    conn.Connect()

    ' Define subscription properties.
    subscription = New MergePullSubscription()
    subscription.ConnectionContext = conn
    subscription.DatabaseName = subscriptionDbName
    subscription.PublisherName = publisherName
    subscription.PublicationDBName = publicationDbName
    subscription.PublicationName = publicationName

    ' If the pull subscription and the job exists, mark the subscription
    ' for reinitialization after upload and start the agent job.
    If subscription.LoadProperties() And (Not subscription.AgentJobId Is Nothing) Then
        subscription.Reinitialize(True)
        subscription.SynchronizeWithJob()
    Else
        ' Do something here if the subscription does not exist.
        Throw New ApplicationException(String.Format( _
         "A subscription to '{0}' does not exists on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Do appropriate error handling here.
    Throw New ApplicationException("The subscription could not be synchronized.", ex)
Finally
    conn.Disconnect()
End Try

Related content

• Reinitialize Subscriptions
• Replication Management Objects Concepts
• Replication Security Best Practices