Reinitialization MS SQL Replication Subscirber may cause data loss

In Transactional Replication Subscriber reinitialization instructs the Distribution Agent to locate and distribute a NEW Snapshot to the Subscriber.  In Merge Replication, the reinitialization is done by the Merge Agent. Depending on the Replication settings, the Subscriber tables may either be 1) dropped, deleted, or 3) truncated resulting in loss of data on the Subscriber.

Now this may be exactly what is needed it the subscriber data is out of sync and the subscription has expired.  But just in case, you may want to take a SQL backup of the subscriber database before making any changes, just…in…case….

For example, we had a customer using Peer-2-Peer Transactional Replication.  The link between servers was down past the publication expiration period.  Before we just initialized the Peer, we used TableDiff.exe utility to find data on “broken” peer that was not on “working” peer.  Customer looked through the data to determine what should manually be saved from the “broken” peer before we reinitialized the peer.

How to run TableDiff utility for ALL replicated (published) tables in SQL 2005 or SQL 2008

Before you Reinit Tran Subscriber:

For Transactional Replication, before you reinititialize a subscriber, verify what happens to the subscriber data when new snapshot is pushed down.

1. Do Articles settings Truncate, Drop, or ignore existing Subscriber data?
2. Does Replication environment allow Subscriber updates?
3. Are there any pending Subscriber updates not yet applied to the Publisher?

If you’re not sure, take a backup of the Subscriber database.  Worst case you may be able to use TableDIFF.exe utility to manually update the Publisher with changed Subscriber data.  Most recent data on Publisher before the new snapshot is created helps ensure Snapshot has all current data before it is pushed to the subscriber.

Article Settings

Verify article settings from Publication properties.

Or you can retrieve these settings using TSQL commands

select dest_table, pre_creation_cmd  from dbo.sysarticles

–example:

dest_table                          pre_creation_cmd

———————————– —————-

Customer                            1

Product                             0

The pre–creation command for DROP TABLE, DELETE TABLE, or TRUNCATE:

• 0 = None.
• 1 = DROP.
• 2 = DELETE.
• 3 = TRUNCATE.

For Peer-2-Peer Replication, examine contents of Mspeer_lsns table to see when updates were last processed.  You can compare this to the MSrepl_transactions table entry_time to see if all transactions were processed.