Compare Replicated Tables for Differences

Problem:- There is a mismatch in the data which is replicated in a transactional replication.

Solution :-

Step 1:- Need to identify the tables
Step 2:- Compare using TableDiff Utility
Step 3:- Update the same on subscriber as publisher wins always. (applicable only if data is less)
else we need to drop and re- add the article.



Replication Diff Tool Command Line Options

        usage: tablediff

          -- Source Options --
 -sourceserver          Source Host
 -sourcedatabase        Source Database
 -sourceschema          Source Schema Name
 -sourcetable           Source Table or View
 -sourceuser            Source Login
 -sourcepassword        Source Password
 -sourcelocked          Lock the source table/view durring tablediff

          -- Destination Options --
 -destinationserver     Destination Host
 -destinationdatabase   Destination Database
 -destinationschema     Destination Schema Name
 -destinationtable      Destination Table or View
 -destinationuser       Destination Login
 -destinationpassword   Destination Password
 -destinationlocked     Lock the destination table/view durring tablediff

          -- Misc Options --
 -t             Timeout
 -c             Column Level Diff
 -f             Generate Fix SQL (You may also specify a file name and path)
 -q             Quick Row Count
 -et            Specify a table to put the errors into
 -dt            Drop the error table if it exists
 -o             Output file
 -b             Number of bytes to read for blob data types
 -strict        Strict compare of source and destination schema
 -rc            Number of retries
 -ri            Retry interval

Example 1:-


C:\Program Files\Microsoft SQL Server\110\COM>tablediff -sourceserver SQBILS -sourcedatabase MSTER_DATA -sourcetable CustomrCRM-destinationserver ACPB01 -destinationdatabase PCTER_DATA_REPL -destinationtable CustomrCRM

Example 2:-
tablediff -sourceserver PRADYOTHNAP02 -sourcedatabase TELIPHONECALLS -sourcetable student -destinationserver santhoshc -destinationdatabase TELICALL_REPLICA destinationtable Student