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.两个表结构完全相同且数据相同
b. 我们来在HOUYAJUN\JHIDCSDBS005上面的表加10行数据
insert into RepTest.dbo.person select 't_04', 't_05' ,'t_06' go 10
再执行上面同样的命令可以看到以下不同:
在此处我们可以看到我对B表修改的列的主键,我们可以根据这些可对表进行操作。在下面会讲述如此对这些数据不一致进行生动生成脚本
c. 如果我们再对表结构进行修改
alter table RepTest.dbo.person add te01 int
再执行上述语句可以看到以下情况:
d. 在此处我们可以看到不同,但不能看到具体的不同,我们再来把刚才多余的数据进行删除看看有什么
delete from RepTest.dbo.person where id in (4 ,5 ,6 ,7 ,8 ,9 ,10,11 ,12,13)
可以看到和上面一样的错误,但是没有出现错误到底是什么不同哦。
参数 -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个
可以看到这个是把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+