SQLServer adds a publishing project from the transaction replication initiated by the backup

Sometimes, you may first initialize a transaction subscription from the backup, and then you need to add a project (Article ). If the subscriber is initialized by a snapshot, you can re-run the snapshot agent to generate a new snapshot. Only the newly added project is output by BCP and then imported to the subscriber BCP. However, if you use backup to restore the created subscription

Sometimes, you may first initialize a transaction subscription from the backup, and then you need to add a project (Article ). If the subscriber is initialized by a snapshot, you can re-run the snapshot agent to generate a new snapshot. Only the newly added project is output by BCP and then imported to the subscriber BCP. However, if you create a subscription by using backup/restoration

Sometimes, you may first initialize a transaction subscription from the backup, and then you need to add a project (Article ). If the subscriber is initialized by a snapshot, you can re-run the snapshot agent to generate a new snapshot. Only the newly added project is output by BCP and then imported to the subscriber BCP. However, if a subscription is created using backup/restoration, its snapshot proxy does not work.

Therefore, when a new table needs to be added to a new table for publishing, there are two methods:

1. only create a new release for that project. Use the release wizard or script and select the default option to push a new snapshot to the subscription. Multiple releases on the same release share one log reading proxy, and each distribution proxy will copy data to the subscriber in parallel.

Or,

2. Use the following steps to add a new project to an existing release and manually synchronize data before restarting replication.

The specific implementation steps of method 2 are as follows:

1) Stop the log reader agent ).

2) Stop changing the data of the new project on the release side (Note: when the data is being copied from this release table, until the table has been added to the release through sp_addarticle, during this period, we must ensure that all changes to this data table are stopped .)

3) generate a table structure script, create a table on the subscriber, and copy the data in the table from the subscriber to the subscriber.

A. For new or small data tables, you can use insert... SELECT * FROM statement directly transfers data

B. Create an SSIS package for tables with a lot of data to create and load tables on a remote server.

4) use the sp_addarticle stored procedure to add the project to the release. (For details about sp_addarticle, see :)

Of course, you can also add a new project to release by copying the relevant wizard. For example:

-Right-click Copy to open the publishing Properties window.

-In the publishing Properties window, click the target table to add to publishing.

-Click "OK" to save.

5) Enable log reading proxy

6) Now you can start to make data changes on this table, because the log reader will track the table data changes and copy them to the subscription end.

Please note that,

-When the backup already includes the table, if it still exists in the subscriber and the table has no schema change, it does not need to be re-created by the subscriber, you only need to ensure manual data synchronization.

-Before enabling the Log Reader Agent, compare the number of rows of the new table added by the publisher and subscriber to determine that the volume of data contained by the publisher and subscriber is the same.