MS SQL Server实用工具说明(bcp/tabdiff)

 

Sql得Dts.dll没有注册
注册方式如下：点击开始--输入CMD运行后，在命令窗口粘贴：cd C:\Program Files\Microsoft SQL Server\90\DTS\Binn\     （注意路径是自己安装SQL2005的目录）

然后到如上的安装目录了，再粘贴：

regsvr32.exe C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dts.dll
（注意本应是如上地址，但因cmd中不允许路径有空格会报错，因此如下处理可避免）

subst w: "C:\Program Files\Microsoft SQL Server\100\DTS\Binn"

（输入后回车，此步是做路径转换，定义“W:\”盘符，以代替有空格的路径）

regsvr32.exe w:\dts.dll     （注册dts.dll，注册成功！终于可以导入数据了）
 

 

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
    [-Ffirst_row] [-Llast_row] [-bbatch_size]
    [-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]
    [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
    [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
    [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
    [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

 

----------------------------------示例-------------------------------------------------

bcp "select XX,XX from 数据库.架构.表名" "queryout" "文件路径\文件名" -c -U登录用户 -P密码 -S服务器IP或实例名

bcp 数据库.架构.表名 in "文件路径\文件名" -c -U登录用户 -P密码 -S服务器IP或实例名

bcp 数据库.架构.表名 out "文件路径\文件名" -c -U登录用户 -P密码 -S服务器IP或实例名

 
 

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 ]
}

 -? ]
Returns the list of supported parameters.

-sourceserver source_server_name[\instance_name]
Is the name of the source server. Specify source_server_name for the default instance of SQL Server. Specify source_server_name\instance_name for a named instance of SQL Server.

-sourcedatabase source_database
Is the name of the source database.

-sourcetable source_table_name
Is the name of the source table being checked.

-sourceschema source_schema_name
The schema owner of the source table. By default, the table owner is assumed to be dbo.

-sourcepassword source_password
Is the password for the login used to connect to the source server using SQL Server Authentication.

Security Note: 
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
Is the login used to connect to the source server using SQL Server Authentication. If source_login is not supplied, then Windows Authentication is used when connecting to the source server. When 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]
Is the name of the destination server. Specify destination_server_name for the default instance of SQL Server. Specify destination_server_name\instance_name for a named instance of SQL Server.

-destinationdatabase subscription_database
Is the name of the destination database.

-destinationtable destination_table
Is the name of the destination table.

-destinationschema destination_schema_name
The schema owner of the destination table. By default, the table owner is assumed to be dbo.

-destinationpassword destination_password
Is the password for the login used to connect to the destination server using SQL Server Authentication.

Security Note: 
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
Is the login used to connect to the destination server using SQL Server Authentication. If destination_login is not supplied, then Windows Authentication is used when connecting 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
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 defaults to the size of the column. Any data above large_object_bytes will not be compared.

-bf number_of_statements
Is 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 bring the table at the destination server into convergence with the table at the source server. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.

-o output_file_name
Is 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
Sets the connection timeout period, in seconds, for connections to the source server and destination server.

 

----------------------------------示例-------------------------------------------------

执行路径：C:\Program Files\Microsoft SQL Server\100\COM

 

tablediff.exe -sourceserver [源实例名] -sourcedatabase [源数据库] -sourceschema [dbo] -sourcetable 源表名 -sourceuser [登录名] -sourcepassword "密码" -destinationserver [目标实例名] -destinationdatabase [目标数据库] -destinationschema [dbo] -destinationtable 目标表名 -destinationuser [登录名] -destinationpassword "密码" /f "文件路径\文件名.sql"