tablediff工具实用

1. tablediff 是什么？

tablediff 实用工具用于比较两个非收敛的表中的数据，它对于排除复制拓扑中的非收敛故障非常有用。

2. tablediff 用哪些用法？

1) . 在充当复制发布服务器的 Microsoft SQL Server 实例中的源表与充当复制订阅服务器的一个或多个 SQL Server 实例中的目标表之间进行逐行比较。
2) . 通过只比较行数和架构可以执行快速比较。
3) . 执行列级比较。
4) . 生成 Transact-SQL 脚本，用以修复目标服务器中的差异，以使源表和目标表实现收敛。
5) . 将结果记录到输出文件或目标数据库的表中。

3. tablediff 语法

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 ]

}

参数说明：

[ -? ]　　返回支持参数的列表。

-sourceserver source_server_name[\instance_name] 源服务器的名称。指定 SQL Server 默认实例的 source_server_name。指定 SQL Server 命名实例的 source_server_name\instance_name。

-sourcedatabase source_database 源数据库的名称。

-sourcetable source_table_name 正在检查的源表的名称。

-sourceschema source_schema_name 源表的架构所有者。默认情况下，表所有者假定为 dbo。

-sourcepassword source_password 使用 SQL Server 身份验证连接到源服务器时所使用的登录帐户的密码。

安全说明
可能的话，请在运行时提供安全凭据。如果必须在脚本文件中存储凭据，则应保护文件以防止未经授权的访问。

-sourceuser source_login 使用 SQL Server 身份验证连接到源服务器时所使用的登录帐户。如果未提供 source_login，则连接到源服务器时使用 Windows 身份验证。请尽可能使用 Windows 身份验证。

-sourcelocked 在使用 TABLOCK 和 HOLDLOCK 表提示的比较过程中锁定源表。

-destinationserver destination_server_name[\instance_name] 目标服务器的名称。指定 SQL Server 默认实例的 destination_server_name。指定 SQL Server 命名实例的 destination_server_name\instance_name。

-destinationdatabase subscription_database 目标数据库的名称。

-destinationtable destination_table 目标表的名称。

-destinationschema destination_schema_name 目标表的架构所有者。默认情况下，表所有者假定为 dbo。

-destinationpassword destination_password 使用 SQL Server 身份验证连接到目标服务器时所使用的登录帐户的密码。

安全说明
可能的话，请在运行时提供安全凭据。如果必须在脚本文件中存储凭据，则应保护文件以防止未经授权的访问。

-destinationuser destination_login 使用 SQL Server 身份验证连接到目标服务器时所使用的登录帐户。如果未提供 destination_login，则连接到该服务器时使用 Windows 身份验证。请尽可能使用 Windows 身份验证。

-destinationlocked 在使用 TABLOCK 和 HOLDLOCK 表提示的比较过程中锁定目标表。

-b large_object_bytes 大型对象数据类型列中要比较的字节数，这些数据类型包括：text、ntext、image、varchar(max)、nvarchar(max) 和 varbinary(max)。 large_object_bytes 默认为列的大小。任何大于 large_object_bytes 的数据将不进行比较。

-bf number_of_statements 使用 -f 选项时要写入到当前 Transact-SQL 脚本文件中的 Transact-SQL 语句数。当 Transact-SQL 语句数超过 number_of_statements 时，将创建一个新的 Transact-SQL 脚本文件。

-c　　比较列级差异。

-dt 　删除 table_name 指定的结果表（如果该表已经存在）。

-et table_name　　指定要创建的结果表的名称。如果该表已经存在，则必须使用 -DT，否则操作将失败。

-f [ file_name ]　　生成 Transact-SQL 脚本，以使目标服务器中的表与源服务器中的表实现收敛。（可选）可以指定生成的 Transact-SQL 脚本文件的名称和路径。如果未指定 file_name，则会在运行实用工具的目录中生成 Transact-SQL 脚本文件。

-o output_file_name　　输出文件的完整名称和路径。

-q 　　通过只比较行数和架构可以执行快速比较。

-rc number_of_retries　　实用工具重试失败操作的次数。

-ri retry_interval　　　两次重试之间的等待间隔（秒）。

-strict　　对源架构和目标架构进行严格比较。

-t connection_timeouts　　设置与源服务器和目标服务器连接时的连接超时时间（秒）。

4. tablediff 注意事项
tablediff 默认存放路径（SQL SERVER 2008） : C:\Program Files\Microsoft SQL Server\100\COM
tablediff 实用工具不能用于非SQL SERVE服务器
若要比较表，您必须要有比较表对象的 SELECT ALL 权限

5. tablediff 使用举例

创建测试环境：

 --SERVER :HOUYAJUN

use TEST

go

create table person

(

id int identity(1,1),

name varchar(50) default('guoqiang'),

[address] varchar(100) default('anhui hefei'),

infro varchar(100)

)

go

insert into person(infro)

select 'test1'

union

select 'test2'

union

select 'test3'

go

select * from TEST.dbo.person with(nolock)

-- SERVER: HOUYAJUN\JHIDCDBS005

use RepTest

go

create table person

(

id int identity(1,1),

name varchar(50) default('guoqiang'),

[address] varchar(100) default('anhui hefei'),

infro varchar(100)

)

go

alter table RepTest.dbo.person add t1 int

insert into person(infro)

select 'test1'

union

select 'test2'

union

select 'test3'

go 2

select * from TEST.dbo.person with(nolock)

参数 -c ：比较列差异

a.两个表结构完全相同且数据相同

tablediff工具实用

b. 我们来在HOUYAJUN\JHIDCSDBS005上面的表加10行数据

insert into RepTest.dbo.person

select 't_04', 't_05' ,'t_06'

go 10

再执行上面同样的命令可以看到以下不同：

tablediff工具实用

在此处我们可以看到我对B表修改的列的主键，我们可以根据这些可对表进行操作。在下面会讲述如此对这些数据不一致进行生动生成脚本

c. 如果我们再对表结构进行修改

alter table RepTest.dbo.person add te01 int

再执行上述语句可以看到以下情况：

tablediff工具实用

d. 在此处我们可以看到不同，但不能看到具体的不同，我们再来把刚才多余的数据进行删除看看有什么

delete from RepTest.dbo.person

where id in (4 ,5 ,6 ,7 ,8 ,9 ,10,11 ,12,13)

tablediff工具实用

可以看到和上面一样的错误，但是没有出现错误到底是什么不同哦。

参数 -q ：通过只比较行数和架构可以执行较快速比较

和上面-c的基本是一样。我们再来把多出的一列删除掉，插入2条记录看一下。

alter table reptest.dbo.person drop column te01
go

insert into RepTest.dbo.person

select 't_04', 't_05' ,'t_06'

go 2

现在应该是表结构一样，数据多了2个

tablediff工具实用

可以看到这个是把2个表的数据行写出来了，一个为3，另外一个为5，没有指定那些不同，但是可以到出2个表是不一样的。

参数 –f: 生成T-SQL脚本，以使目标服务器上的表与源服务器上的表实现收敛

参数 –o：输出文件的完整名称和路径。可以输出日志信息。

在这里我们没有看到错误信息了，因为错误信息已经写在了log里面，而persondiff则为对应的SQL修改语句。

log 信息为：

Table [test].[dbo].[person] on houyajun and Table [reptest].[dbo].[person] on houyajun\jhidcdbs005 have 2 differences.

Fix SQL written to d:persondiff.sql.

Err    id    Col

Dest. Only    14

Dest. Only    15

The requested operation took 0.154 seconds.

persondiff.sql 的信息为：

-- Host: houyajun\jhidcdbs005

-- Database: [reptest]

-- Table: [dbo].[person]

SET IDENTITY_INSERT [dbo].[person] ON

DELETE FROM [dbo].[person] WHERE [id] = 14

DELETE FROM [dbo].[person] WHERE [id] = 15

SET IDENTITY_INSERT [dbo].[person] OFF

附一个查询，可以直接粘在dos下，然后修改一下对应的参数：

DECLARE @sourceserver sysname

DECLARE @sourcedatabase sysname

DECLARE @sourceschema sysname

DECLARE @sourcetable nvarchar(1000)

DECLARE @destinationserver sysname

DECLARE @destinationdatabase sysname

DECLARE @destinationschema sysname

DECLARE @destinationtable nvarchar(1000)

select @sourceserver = 'HOUYAJUN'

,@sourcedatabase = 'TEST'

,@sourceschema = 'dbo'

,@sourcetable = 'REPET'

,@destinationserver ='HOUYAJUN\SS005'

,@destinationdatabase =  'TEST'

,@destinationschema = 'dbo'

,@destinationtable = 'REPET'

SELECT 'tablediff -sourceserver ' + @sourceserver + ' -sourcedatabase ' +@sourcedatabase+ ' -sourceschema ' +@sourceschema + ' -sourcetable ' +@sourcetable + ' -destinationserver ' +@destinationserver+ ' -destinationdatabase ' +@destinationdatabase+

秒客网

tablediff工具实用

相关文章