tablediff utility

            Tablediff is an inbuilt SQL Server utility that is used to compare data between two tables across same or different servers. This utility is invoked from command prompt and is usually found in C:\Program Files\Microsoft SQL Server\100\COM folder. The best use of this utility is exhibited when you use it to compare tables that are involved in replication. The following tasks can be performed with the utility.

• A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
• Perform a fast comparison by only comparing row counts and schema. 
• Perform column-level comparisons. 
• Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence. 
• Log results to an output file or into a table in the destination database.

Tablediff has various arguments that can be passed to it to get the desired results.

The parameters are discussed as follows.

-sourceserver source_server_name[\instance_name]

The name of the source server. You need to specify the source instance name to this argument.

-sourcedatabase source_database

The name of the source database.

-sourcetable source_table_name

The name of the source table being compared.

-sourceschema source_schema_name

The schema owner of the source table. By default, the table owner is dbo.

-sourcepassword source_password

The password for the login used to connect to the source server using SQL Server Authentication.

-sourceuser source_login

The login used to connect to the source server using SQL Server Authentication. If source_login is not supplied, the utility uses Windows Authentication to connect to the source server. Whenever possible, use Windows Authentication.

-sourcelocked

The source table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-destinationserver destination_server_name[\instance_name]

The name of the destination server. You need to specify the source instance name to this argument.

-destinationdatabase subscription_database

The name of the destination database.

-destinationtable destination_table

The name of the destination table.

-destinationschema destination_schema_name

The schema owner of the destination table. By default, the table owner is dbo.

-destinationpassword destination_password

The password for the login used to connect to the destination server using SQL Server Authentication.

-destinationuser destination_login

The login used to connect to the destination server using SQL Server Authentication. If destination_login is not supplied, the utility uses Windows Authentication to the server. When possible, use Windows Authentication.

-destinationlocked

The destination table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-b large_object_bytes

            The number of bytes to compare for large object data type columns, which includes: text, ntext, image, varchar(max), nvarchar(max) andvarbinary(max). large_object_bytes defaults to the size of the column. Any data above large_object_bytes will not be compared.

-bf number_of_statements

The number of Transact-SQL statements to write to the current Transact-SQL script file when the -f option is used. When the number of Transact-SQL statements exceeds number_of_statements, a new Transact-SQL script file is created.

-c

Compare column-level differences.

-dt

Drop the result table specified by table_name, if the table already exists.

-et table_name

Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]

Generates a Transact-SQL script to synchronize table that are being compared. You can optionally specify a name and path for the generated Transact-SQL script file.

-o output_file_name

The full name and path of the output file.

-q

Perform a fast comparison by only comparing row counts and schema.

-rc number_of_retries

Number of times that the utility retries a failed operation.

-ri retry_interval

Interval, in seconds, to wait between retries.

-strict

Source and destination schema are strictly compared.

-t connection_timeouts

Is the connection timeout period, in seconds, for connections to the source server and destination server.

Restrictions:

            The source table in the comparison must contain at least one primary key, identity, or ROWGUID column. When you use the -strict option, the destination table must also have a primary key, identity, or ROWGUID column.

The Transact-SQL script generated to bring the destination table into convergence does not include the following data types:

• varchar(max) 
• nvarchar(max) 
• varbinary(max) 
• timestamp 
• xml 
• text 
• ntext 
• image 

Permissions Needed:

1.      You need select permissions on the tables being compared.

2.     To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.

3.     To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.

4.     To use the -o or -f options, you must have write permissions to the specified file directory location.