tablediff 公用程式tablediff Utility

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

tablediff 公用程式用來比較兩份資料表之資料的非聚合狀況,當進行複寫拓撲中之非聚合狀況的疑難排解時,它尤其有用。The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. 您可以在命令提示字元之下,或在批次檔中,利用這個公用程式來執行下列工作:This utility can be used from the command prompt or in a batch file to perform the following tasks:

  • 在扮演複寫簽發者之 MicrosoftMicrosoft SQL ServerSQL Server 執行個體的來源資料表,與扮演複製訂閱者的一或多個 SQL ServerSQL Server 執行個體的目的地資料表之間,每個資料列做比較。A row by row comparison between a source table in an instance of MicrosoftMicrosoft SQL ServerSQL Server acting as a replication Publisher and the destination table at one or more instances of SQL ServerSQL Server acting as replication Subscribers.

  • 執行快速比較,只比較資料列計數和結構描述。Perform a fast comparison by only comparing row counts and schema.

  • 進行資料行層級的比較。Perform column-level comparisons.

  • 產生 Transact-SQLTransact-SQL 指令碼來修正目的地伺服器不一致的情形,以便聚合來源和目的地資料表。Generate a Transact-SQLTransact-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.

語法Syntax

  
tablediff   
[ -? ] |   
{  
        -sourceserver source_server_name[\instance_name]  
        -sourcedatabase source_database  
        -sourcetable source_table_name   
    [ -sourceschema source_schema_name ]  
    [ -sourcepassword source_password ]  
    [ -sourceuser source_login ]  
    [ -sourcelocked ]  
        -destinationserver destination_server_name[\instance_name]  
        -destinationdatabase subscription_database   
        -destinationtable destination_table   
    [ -destinationschema destination_schema_name ]  
    [ -destinationpassword destination_password ]  
    [ -destinationuser destination_login ]  
    [ -destinationlocked ]  
    [ -b large_object_bytes ]   
    [ -bf number_of_statements ]   
    [ -c ]   
    [ -dt ]   
    [ -et table_name ]   
    [ -f [ file_name ] ]   
    [ -o output_file_name ]   
    [ -q ]   
    [ -rc number_of_retries ]   
    [ -ri retry_interval ]   
    [ -strict ]  
    [ -t connection_timeouts ]   
}  

引數Arguments

[ -?[ -? ]]
傳回支援的參數清單。Returns the list of supported parameters.

-sourceserver source_server_name[ \ instance_name]-sourceserver source_server_name[\instance_name]
這是來源伺服器的名稱。Is the name of the source server. 指定 預設執行個體的 source_server_name SQL ServerSQL ServerSpecify source_server_name for the default instance of SQL ServerSQL Server. 指定 具名執行個體的 \ source_server_name instance_name SQL ServerSQL ServerSpecify source_server_name\instance_name for a named instance of SQL ServerSQL Server.

-sourcedatabase source_database-sourcedatabase source_database
這是來源資料庫的名稱。Is the name of the source database.

-sourcetable source_table_name-sourcetable source_table_name
這是所檢查的來源資料表的名稱。Is the name of the source table being checked.

-sourceschema source_schema_name-sourceschema source_schema_name
來源資料表的結構描述擁有者。The schema owner of the source table. 依預設,資料表擁有者假設為 dbo。By default, the table owner is assumed to be dbo.

-sourcepassword source_password-sourcepassword source_password
這是用來連接使用 SQL ServerSQL Server 驗證之來源伺服器的登入密碼。Is the password for the login used to connect to the source server using SQL ServerSQL Server Authentication.

重要

可能的話,請在執行階段提供安全性認證。When possible, supply security credentials at runtime. 如果您必須將認證儲存在指令碼檔案中,您應該維護這個檔案的安全性,使他人無法在未獲授權的情況下擅自存取。If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

-sourceuser source_login-sourceuser source_login
這是用來連接使用 SQL ServerSQL Server 驗證之來源伺服器的登入。Is the login used to connect to the source server using SQL ServerSQL Server Authentication. 如果未提供 source_login ,在連接到來源伺服器時,會使用 Windows 驗證。If source_login is not supplied, then Windows Authentication is used when connecting to the source server. 儘可能使用 Windows 驗證。When possible, use Windows authentication.

-sourcelocked-sourcelocked
在比較期間,來源資料表以 TABLOCK 和 HOLDLOCK 資料表提示鎖定。The source table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-destinationserver destination_server_name[ \ instance_name]-destinationserver destination_server_name[\instance_name]
這是目的地伺服器的名稱。Is the name of the destination server. 指定 預設執行個體的 destination_server_name SQL ServerSQL ServerSpecify destination_server_name for the default instance of SQL ServerSQL Server. 指定 具名執行個體的 \ destination_server_name instance_name SQL ServerSQL ServerSpecify destination_server_name\instance_name for a named instance of SQL ServerSQL Server.

-destinationdatabase subscription_database-destinationdatabase subscription_database
這是目的地資料庫的名稱。Is the name of the destination database.

-destinationtable destination_table-destinationtable destination_table
這是目的地資料表的名稱。Is the name of the destination table.

-destinationschema destination_schema_name-destinationschema destination_schema_name
目的地資料表的結構描述擁有者。The schema owner of the destination table. 依預設,資料表擁有者假設為 dbo。By default, the table owner is assumed to be dbo.

-destinationpassword destination_password-destinationpassword destination_password
這是用來連接使用 SQL ServerSQL Server 驗證之目的地伺服器的登入密碼。Is the password for the login used to connect to the destination server using SQL ServerSQL Server Authentication.

重要

可能的話,請在執行階段提供安全性認證。When possible, supply security credentials at runtime. 如果您必須將認證儲存在指令碼檔案中,您應該維護這個檔案的安全性,使他人無法在未獲授權的情況下擅自存取。If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

-destinationuser destination_login-destinationuser destination_login
這是用來連接使用 SQL ServerSQL Server 驗證之目的地伺服器的登入。Is the login used to connect to the destination server using SQL ServerSQL Server Authentication. 如果未提供 destination_login ,在連接到伺服器時,會使用 Windows 驗證。If destination_login is not supplied, then Windows Authentication is used when connecting to the server. 儘可能使用 Windows 驗證。When possible, use Windows authentication.

-destinationlocked-destinationlocked
在比較期間,目的地資料表以 TABLOCK 和 HOLDLOCK 資料表提示鎖定。The destination table is locked during the comparison using the TABLOCK and HOLDLOCK table hints.

-b large_object_bytes-b large_object_bytes
是大型物件資料類型資料行要比較的位元組數,包括︰ textntextimagevarchar(max)nvarchar(max)varbinary(max)Is the number of bytes to compare for large object data type columns, which includes: text, ntext, image, varchar(max), nvarchar(max) and varbinary(max). large_object_bytes 預設為資料行的大小。large_object_bytes defaults to the size of the column. 不比較任何超出 large_object_bytes 的資料。Any data above large_object_bytes will not be compared.

-bf number_of_statements-bf number_of_statements
這是使用 Transact-SQLTransact-SQL -f Transact-SQLTransact-SQL 選項時要寫入目前 指令碼檔案中的 陳述式數目。Is the number of Transact-SQLTransact-SQL statements to write to the current Transact-SQLTransact-SQL script file when the -f option is used. Transact-SQLTransact-SQL 陳述式數目超出 number_of_statements時,會建立新的 Transact-SQLTransact-SQL 指令碼檔案。When the number of Transact-SQLTransact-SQL statements exceeds number_of_statements, a new Transact-SQLTransact-SQL script file is created.

-c-c
比較資料行層級的差異。Compare column-level differences.

-dt-dt
卸除 table_name指定的結果資料表 (如果該資料表已存在的話)。Drop the result table specified by table_name, if the table already exists.

-et table_name-et table_name
指定要建立的結果資料表名稱。Specifies the name of the result table to create. 如果這份資料表已經存在,就必須使用 -DT ,否則作業會失敗。If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]-f [ file_name ]
產生一份 Transact-SQLTransact-SQL 指令碼來聚合目的地伺服器的資料表與來源伺服器的資料表。Generates a Transact-SQLTransact-SQL script to bring the table at the destination server into convergence with the table at the source server. 您可以選擇性地為所產生的 Transact-SQLTransact-SQL 指令碼檔案指定名稱和路徑。You can optionally specify a name and path for the generated Transact-SQLTransact-SQL script file. 如果未指定 file_nameTransact-SQLTransact-SQL 指令碼檔案會產生在執行公用程式的目錄中。If file_name is not specified, the Transact-SQLTransact-SQL script file is generated in the directory where the utility runs.

-o output_file_name-o output_file_name
這是輸出檔的完整名稱和路徑。Is the full name and path of the output file.

-q-q
執行快速比較,只比較資料列計數和結構描述。Perform a fast comparison by only comparing row counts and schema.

-rc number_of_retries-rc number_of_retries
公用程式重試失敗作業的次數。Number of times that the utility retries a failed operation.

-ri retry_interval-ri retry_interval
兩次重試之間等待的間隔秒數。Interval, in seconds, to wait between retries.

-strict-strict
嚴格比較來源和目的地結構描述。Source and destination schema are strictly compared.

-t connection_timeouts-t connection_timeouts
設定通往來源伺服器和目的地伺服器之連接的連接逾時期限 (以秒為單位)。Sets the connection timeout period, in seconds, for connections to the source server and destination server.

傳回值Return Value

ReplTest1Value DescriptionDescription
00 成功Success
11 嚴重錯誤Critical error
22 資料表差異Table differences

RemarksRemarks

tablediff 公用程式不能與非 SQL ServerSQL Server 伺服器搭配使用。The tablediff utility cannot be used with non- SQL ServerSQL Server servers.

不支援具有 sql_variant 資料類型資料行的資料表。Tables with sql_variant data type columns are not supported.

根據預設, tablediff 公用程式支援來源和目的地資料行之間的下列資料類型對應。By default, the tablediff utility supports the following data type mappings between source and destination columns.

來源資料類型Source data type 目的地資料類型Destination data type
tinyinttinyint smallintintbigintsmallint, int, or bigint
smallintsmallint intbigintint or bigint
intint bigintbigint
timestamptimestamp varbinaryvarbinary
varchar(max)varchar(max) texttext
nvarchar(max)nvarchar(max) ntextntext
varbinary(max)varbinary(max) imageimage
texttext varchar(max)varchar(max)
ntextntext nvarchar(max)nvarchar(max)
imageimage varbinary(max)varbinary(max)

使用 -strict 選項可禁止這些對應並執行嚴格驗證。Use the -strict option to disallow these mappings and perform a strict validation.

比較中的來源資料表必須至少包含一個主索引鍵、身分識別或 ROWGUID 資料行。The source table in the comparison must contain at least one primary key, identity, or ROWGUID column. 當您使用 -strict 選項時,目的地資料表也必須有主索引鍵、識別或 ROWGUID 資料行。When you use the -strict option, the destination table must also have a primary key, identity, or ROWGUID column.

為了使目的地資料表達到聚合而產生的 Transact-SQLTransact-SQL 指令碼不包括下列資料類型:The Transact-SQLTransact-SQL script generated to bring the destination table into convergence does not include the following data types:

  • varchar(max)varchar(max)

  • nvarchar(max)nvarchar(max)

  • varbinary(max)varbinary(max)

  • timestamptimestamp

  • xmlxml

  • texttext

  • ntextntext

  • imageimage

權限Permissions

若要比較資料表,您必須具有所比較之資料表物件的 SELECT ALL 權限。To compare tables, you need SELECT ALL permissions on the table objects being compared.

若要使用 -et 選項,您必須是 db_owner 固定資料庫角色的成員,或至少具有訂閱資料庫中的 CREATE TABLE 權限,或目的地伺服器之目的地擁有者結構描述的 ALTER 權限。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.

若要使用 -dt 選項,您必須是 db_owner 固定資料庫角色的成員,或至少具有目的地伺服器之目的地擁有者結構描述的 ALTER 權限。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.

若要使用 -o-f 選項,您必須具有指定檔案目錄位置的寫入權限。To use the -o or -f options, you must have write permissions to the specified file directory location.

另請參閱See Also

比較複寫資料表的差異 (複寫程式設計)Compare Replicated Tables for Differences (Replication Programming)