Compare and Fix Replicated Data

When you suspect a subscriber has drifted out of sync with its publisher, this script generates the full repair batch for you. Run it on the publisher database after setting the publication name, subscriber server/database, SQL Server install path and a temp folder at the top. It walks every user table in the publication (smallest first), and PRINTs a tablediff command to produce a fix-up script per table plus a sqlcmd invocation that applies it on the subscriber - so running the output once through cmd re-syncs the whole publication.

SET NOCOUNT ON;

DECLARE @PublicationName	nVarChar(255) SET @PublicationName		= 'publicaton_name'
DECLARE @SQLServerPath		nVarChar(255) SET @SQLServerPath		= 'C:\Program Files\Microsoft SQL Server\100\'
DECLARE @SubscriberServer	nVarChar(255) SET @SubscriberServer		= 'subscriber_server'
DECLARE @SubscriberDatabase nVarChar(255) SET @SubscriberDatabase	= 'subscriber_database'
DECLARE @TempPath			nVarChar(255) SET @TempPath				= 'C:\temp\'

DECLARE @TableName SysName
DECLARE @FileName nVarChar(255)
DECLARE @Rows BigInt
DECLARE @SQLCmd nVarChar(255) SET @SQLCmd = @SQLServerPath + 'Tools\Binn\sqlcmd.exe'
DECLARE @TableDiff nVarChar(255) SET @TableDiff = @SQLServerPath + 'COM\tablediff.exe'

DECLARE Tables CURSOR FAST_FORWARD FOR
	SELECT
		SysO.name,
		(
			SELECT
				CONVERT(bigint, SysI.[rows])
			FROM
				sysindexes as SysI
			WHERE
				SysI.id = SysO.object_id
				AND SysI.indid < 2
		) as [Rows]
	FROM
		sys.objects as SysO
	INNER JOIN sysarticles as Art
		ON Art.objid = SysO.object_id
	INNER JOIN syspublications as Pub
		ON Pub.pubid = Art.pubid
	WHERE
		SysO.type = 'U'
		AND SysO.is_ms_shipped = 0
		AND Pub.name = @PublicationName
	ORDER BY
		[Rows] ASC
OPEN Tables

PRINT '@Echo Off'

PRINT 'Del "' + @TempPath + '*.sql"'

FETCH FROM Tables INTO @TableName, @Rows

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	------------------------------------------------------------------------------------------------------------------------------------------------------------
    
	SET @FileName = @TempPath + @TableName + '.sql'

	PRINT 'ECHO Table [' + @TableName + '], Rowcount: ' + Cast(@Rows as VarChar)

	PRINT '"' + @TableDiff + '" -sourceserver ' + @@servername + ' -sourcedatabase ' + DB_NAME() + ' -sourcetable ' + @TableName + ' -destinationserver ' + @SubscriberServer + ' -destinationdatabase ' + @SubscriberDatabase + ' -destinationtable ' + @TableName + ' -f "' + @FileName + '"'

	PRINT 'IF EXIST "' + @FileName + '" ('
	PRINT '"' + @SQLCmd + '" -S ' + @SubscriberServer + ' -d ' + @SubscriberDatabase + ' -i "' + @FileName + '"'
	PRINT ')'
   		
	PRINT ''

	------------------------------------------------------------------------------------------------------------------------------------------------------------
	FETCH FROM Tables INTO @TableName, @Rows
END--WHILE

CLOSE Tables DEALLOCATE Tables