陕西财经职业学院校草:tablediff 实用工具 使用技巧

tablediff 实用工具 使用技巧 来源：中国自学编程网   发布日期：2008-07-02   1 tablediff是什么？
tablediff 实用工具用于比较两个非收敛的表中的数据，它对于排除复制拓扑中的非收敛故障非常有用。
该工具小巧，SQL Server 2005免费提供的，非常有用！

2 tablediff有哪些用法？
（1）在充当复制发布服务器的 Microsoft SQL Server 实例中的源表与充当复制订阅服务器的一个或多个 SQL Server 实例上的目标表之间进行逐行比较。
（2）通过只比较行数和架构可以执行快速比较。
（3）同时比较多个目标服务器上的目标表。
（4）执行列级比较。
（5）生成 Transact-SQL 脚本，用以修复目标服务器上的差异，以使源表和目标表实现收敛。
（6）将结果记录到输出文件或目标数据库的表中。
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 ]
[ , ... n ]
}
[ -b large_object_bytes ]
[ -c ]
[ -dt ]
[ -et table_name ]
[ -f ]
[ -o output_file_name ]
[ -q ]
[ -t connection_timeouts ]
}


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

3 tablediff使用举例

--参数-c：比较列级差异
tablediff -sourceserver "qiangguo\ods" -sourcedatabase "DB1" -sourcetable "person" -sourceschema "dbo" -destinationserver "qiangguo\ods" -destinationdatabase "DB2" -destinationtable "person" -destinationschema "dbo" -c

--参数-q：通过只比较行数和架构可以执行快速比较
tablediff -sourceserver "qiangguo\ods" -sourcedatabase "DB1" -sourcetable "person" -sourceschema "dbo" -destinationserver "qiangguo\ods" -destinationdatabase "DB2" -destinationtable "person" -destinationschema "dbo" -q

--参数-f：生成 Transact-SQL 脚本，以使目标服务器上的表与源服务器上的表实现收敛
--参数-o：输出文件的完整名称和路径。可以输出日志信息。
tablediff -sourceserver "qiangguo\ods" -sourcedatabase "DB1" -sourcetable "person" -sourceschema "dbo" -destinationserver "qiangguo\ods" -destinationdatabase "DB2" -destinationtable "person" -destinationschema "dbo" -f "C:\persondiff.sql" -o "C:\log.txt"

-----------------------
--创建测试环境
-----------------------
use master;
go

create database DB1;
go
create database DB2;
go

use DB1;
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 DB1.dbo.person

use DB2;
go

create table person
(
id int identity(1,1),
name varchar(50) default('guoqiang'),
address varchar(100) default('anhui fuyang'),
infro varchar(100)
)
go

insert into person(infro)
select 'test1'
union
select 'test2'
union
select 'test3'
go

--select * from DB2.dbo.person


select a.id,a.name,a.address,a.infro,b.id,b.name,b.address,b.infro
from DB1.dbo.person a,DB2.dbo.person b
where a.id = b.id


------------------------------
--详细过程如下所示
------------------------------

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\guoqiang>cd C:\Program Files\Microsoft SQL Server\90\COM

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "qiangguo\ods" -sourcedatabase "DB1" -sourcetable "pers
n" -sourceschema "dbo" -destinationserver "qiangguo\ods" -destinationdatabase "DB2" -destinationtable "person" -destinations
hema "dbo" -c

User-specified agent parameter values:
-sourceserver qiangguo\ods
-sourcedatabase DB1
-sourcetable person
-sourceschema dbo
-destinationserver qiangguo\ods
-destinationdatabase DB2
-destinationtable person
-destinationschema dbo
-c

Table [DB1].[dbo].[person] on qiangguo\ods and Table [DB2].[dbo].[person] on qiangguo\ods have 3 differences.
Err id Col
Mismatch 1 address
Mismatch 2 address
Mismatch 3 address
The requested operation took 0.203125 seconds.

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "qiangguo\ods" -sourcedatabase "DB1" -sourcetable "pers
n" -sourceschema "dbo" -destinationserver "qiangguo\ods" -destinationdatabase "DB2" -destinationtable "person" -destinations
hema "dbo" -q

User-specified agent parameter values:
-sourceserver qiangguo\ods
-sourcedatabase DB1
-sourcetable person
-sourceschema dbo
-destinationserver qiangguo\ods
-destinationdatabase DB2
-destinationtable person
-destinationschema dbo
-q

Table [DB1].[dbo].[person] on qiangguo\ods and Table [DB2].[dbo].[person] on qiangguo\ods are identical.
Table [DB1].[dbo].[person] on qiangguo\ods has 3 rows.
Table [DB2].[dbo].[person] on qiangguo\ods has 3 rows.
The requested operation took 0.21875 seconds.

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "qiangguo\ods" -sourcedatabase "DB1" -sourcetable "pers
n" -sourceschema "dbo" -destinationserver "qiangguo\ods" -destinationdatabase "DB2" -destinationtable "person" -destinations
hema "dbo" -f "C:\persondiff.sql" -o "C:\log.txt"

User-specified agent parameter values:
-sourceserver qiangguo\ods
-sourcedatabase DB1
-sourcetable person
-sourceschema dbo
-destinationserver qiangguo\ods
-destinationdatabase DB2
-destinationtable person
-destinationschema dbo
-f C:\persondiff.sql
-o C:\log.txt


C:\Program Files\Microsoft SQL Server\90\COM>

--persondiff.sql内容如下：
-- Host: qiangguo\ods
-- Database: [DB2]
-- Table: [dbo].[person]
SET IDENTITY_INSERT [dbo].[person] ON
UPDATE [dbo].[person] SET [address]='anhui hefei' WHERE [id] = 1
UPDATE [dbo].[person] SET [address]='anhui hefei' WHERE [id] = 2
UPDATE [dbo].[person] SET [address]='anhui hefei' WHERE [id] = 3
SET IDENTITY_INSERT [dbo].[person] OFF

--log.txt内容如下：
Table [DB1].[dbo].[person] on qiangguo\ods and Table [DB2].[dbo].[person] on qiangguo\ods have 3 differences.
Fix SQL written to C:\persondiff.sql.
Err id Col
Mismatch 1 address
Mismatch 2 address
Mismatch 3 address
The requested operation took 0.1875 seconds.