Simple use of BCP backup tools

I checked the information online today and learned about BCP's simple transfer and transfer operations.

1. BCP is a database table-level data backup tool provided by Sybase. Therefore, it operates on tables.

2. Common statements are as follows:

Export table: BCP dbname .. tablename out C:/temp/filename-U sa-P password-s servername-C

Dbname database name; tablename table name;-u: Login Name;-P: Login Password;-C: export data using visible text. There are two vertices between the data name and table name.

Import table: BCP dbname .. tablename in C:/temp/filename-U sa-P password-s servername-C

Note: The difference between the two is only the difference between out and in.

Iii. use SQL to generate BCP commands to quickly back up/restore all your data (only for Sybase and MSSQLServer databases)

The BCP command is a tool used by Sybase and ms SQL Server to back up and restore data. It is easy to use and supports fast backup/recovery. Writing batch processing is cumbersome when there are too many tables. You can use the following method to quickly generate a BCP batch

Select ''bcp database .. ''+ name + ''out'' +''/data/''+ name + ''. out ''+''-n-sservername-USA-pxxx ''from database .. sysobjects where type = ''u''

Replace the above database with the name of the database you want to back up, and change-sservername to the corresponding SQL server name-pxxx to replace xxx with the actual SA password. The above syntax is used to back up data, change the out in the first line to in to generate a batch of data recovery.

The preceding can be executed in iSQL of Sybase or ms SQL Server (iSQL has changed to query analyzer in ms SQL Server 7.0 ), then copy the execution result to notepad through the clipboard (do not copy the title of the result set) and save it as a bat file. When executing the final BAT file, you need to create a data subdirectory in the directory where BAT is located. The *. Out file for backup will be stored in this subdirectory. You can also run the command in the database administration of Pb (which can be executed only after the last supplement; No.), save the result as the text type, and change it to the BAT file.

Iv. BCP utility syntax (reproduced)
BCP utility in Microsoft? SQL Server? 2000 copy data between an instance and a data file in the format specified by the user.

Syntax
BCP {[[database_name.] [owner].] {table_name | view_name} | "query "}
{In | Out | queryout | format} data_file
[-M max_errors] [-F format_file] [-e err_file]
[-F first_row] [-l last_row] [-B batch_size]
[-N] [-C] [-W] [-N] [-V (60 | 65 | 70)] [-6]
[-Q] [-C code_page] [-T field_term] [-r row_term]
[-I input_file] [-O output_file] [-A packet_size]
[-S SERVER_NAME [/instance_name] [-u login_id] [-P Password]
[-T] [-V] [-R] [-K] [-E] [-h "hint [,... n]"]

Parameters
Database_name

Name of the database where the specified table or view is located. If not specified, the default database is used.

Owner

Name of the table or view owner. If you have a specified table or view for the large-capacity copy operation, the owner is optional. If you do not specify the owner and do not have the specified table or view, Microsoft? SQL Server? 2000 the system will return an error message and cancel the large-capacity copy operation.

Table_name

The name of the target table when data is copied to SQL Server (in) and the name of the source table when data is copied from SQL Server (out.

View_name

Is the name of the target view for copying data to SQL Server (in) and the name of the source view for (out) Copying data from SQL Server. Only a view with all columns referencing the same table can be used as the target view. For more information about the restrictions on copying data to a view, see insert.

Query

Is a Transact-SQL query that returns a result set. If the query returns multiple result sets, for example, the SELECT statement of the specified compute clause, only the first result set will be copied to the data file, and the subsequent result set will be ignored. Use double quotation marks to cause a query statement, and use single quotation marks to cause any content embedded in the query statement. You must specify queryout when copying data from a large volume in the query.

In | Out | queryout | format

Specifies the direction of large-capacity replication. In refers to copying from a file to a database table or view, and out refers to copying from a database table or view to a file. Queryout must be specified only when data is replicated from a large capacity in the query. Format creates a format file based on the specified options (-N,-C,-W,-6, or-N) and the table or view separator. If format is used, you must specify the-F option.

 
Note: The BCP utility in Microsoft SQL Server 6.5 does not support copying large data volumes to tables that contain SQL _variant or bigint data types.

Data_file

The complete path of the data file used to copy a large table or view to a disk (or from a disk. When you copy large data volumes to SQL Server, this data file contains data that will be copied to a specified table or view. When copying data from SQL Server in large capacity, the data file contains data copied from tables or views. The path can contain 1 to 255 characters.

-M max_errors

Specifies the maximum number of errors that may occur before the large-capacity copy operation is canceled. Every row that BCP cannot copy will be ignored and counted as an error. If this option is not included, the default value is 10.

-F format_file

Specifies the full path of the format file. The format file contains the storage response when BCP is used in the same table or view. This option is used when you use a large-capacity copy of the format file created by the format option to copy in or out data. Format File Creation is optional. After several format questions are prompted, BCP will prompt whether to save and answer in the format file. The default file name is BCP. FMT. When copying data in large capacity, BCP can reference a format file, so you do not have to re-enter the previous answer. If this option is not used and-N,-C,-W,-6, or-N is not specified, BCP will prompt you to enter the format information.

-E err_file

Specifies the full path of the error file, which is used to store all rows that BCP cannot transmit from the file to the database. The error message from BCP is sent to the user workstation. If this option is not used, no error file is created.

-F first_row

Specify the ordinal number of the first row to be replicated in large capacity. The default value is 1, indicating the first row of the specified data file.

-L last_row

Specify the ordinal number of the last row to be copied in large capacity. The default value is 0, indicating the last row in the specified data file.

-B batch_size

Specifies the number of rows in each batch of copied data. Each batch is copied to the server as a transaction. SQL Server submits or rolls back each batch of transactions (when a failure occurs. By default, all data in the specified data file is copied as a batch. Do not use it with the-h "rows_per_batch = BB" option.

-N

Use the local (database) Data Type of the data to perform the large-capacity copy operation. This option does not prompt you to enter each field. It uses the local value.

-C

Use the character data type to perform a large-capacity copy operation. This option does not prompt you to enter each field. It uses char as the storage type, without a prefix,/t (Tab) as the field separator, And/N (line break) as the line terminator.

-W

Use Unicode characters to perform large-capacity copy operations. This option does not prompt you to enter each field. It uses nchar as the storage type, without a prefix,/t (Tab) as the field separator, And/N (line break) as the line terminator. It cannot be used in SQL Server 6.5 or earlier versions.

-N

The local (database) data type used for non-character data and the Unicode Character Type Used for character data are copied in large capacity. This is a higher performance option that can replace the-W option, with the aim of transferring data from one SQL Server to another using data files. It does not prompt you to enter each field. This option can be used to transmit data that contains ANSI extended characters and to take advantage of the performance of the local mode. You cannot use the-n option in SQL Server 6.5 or earlier versions.

-V (60 | 65 | 70)

Use the data types in earlier versions of SQL Server to perform large-capacity replication. This option is used with the character (-C) or local (-N) format. This option does not prompt you to enter each field. It uses the default value. For example, to copy large data formats supported by the BCP utility in SQL Server 6.5 (but not supported by ODBC) to SQL Server 2000, you can use the-V 65 parameter.

 
Important to copy data from SQL server to a large data file, even if-V is specified, the BCP utility does not generate a date format of SQL Server 6.0 or SQL Server 6.5 for any datetime or smalldatetime data. The date is always written in ODBC format. In addition, SQL Server 6.5 or earlier versions do not support Empty Bit Data. Therefore, the null value in the bit column is set to 0.

-6

Use the SQL Server 6.0 or SQL Server 6.5 data type to perform a large-capacity copy operation. To maintain backward compatibility. Use the-V option instead.

-Q

Run the set quoted_identifiers on statement in the connection between the BCP utility and the SQL server instance. Use this option to specify the name of a database, owner, table, or view that contains spaces or quotation marks. Enclose the entire table name or view name in double quotation marks.

-C code_page

To maintain backward compatibility. In the format file or interactive BCP, specify a sorting rule name for each column.

Specifies the data code page in the data file. Code_page is useful only when the data contains char, varchar, or text columns whose character values are greater than 127 or less than 32.

Code Page value description
Acp ansi/Microsoft Windows? (ISO 1252 ).
The default code page used by the OEM Program. If-C is not specified, this is the default code page used by BCP.
Raw does not convert data from one code page to another. Because no conversion occurs, this is the fastest option.
<Value> a specific code page number, such as 850.

-T field_term

Specifies the field Terminator. The default field Terminator is/t (Tab ). Use this parameter to replace the default field Terminator.

-R row_term

Specifies the line terminator. The default line terminator is/N (line break ). Use this parameter to replace the default line terminator.

-I input_file

Specify the name of the response file. When the interactive mode (-N,-C,-W,-6, or-N is not specified) is used for large-capacity replication, the response file contains the response to each field of command prompt.

-O output_file

Specifies the name of the file that receives BCP output (redirection from command prompt.

-A packet_size

Specify the number of bytes of each network packet sent to and from the server. You can use the SQL Server Enterprise Manager (or sp_configure system stored procedure) to set server configuration options. However, you can use this option to replace the server configuration option. Packet_size can be set to 4096 to 65535 bytes. The default value is 4096.

Increasing the data packet size can improve the performance of large-capacity replication operations. If a large data packet is required, the default setting is used. The performance statistics generated by BCP show the size of the data packets used.

-S SERVER_NAME [/instance_name]

Specify the SQL server instance to connect. Specify SERVER_NAME to connect to the default instance of SQL Server on this server. Specify SERVER_NAME/instance_name to connect to the SQL Server 2000 instance on the server. If no server is specified, BCP is connected to the default instance of SQL server on the local computer. This option is required when a remote computer on the network executes BCP.

-U login_id

The logon ID used to connect to SQL Server.

-P Password

Password of the logon ID. If this option is not used, BCP will prompt you to enter the password. If this option is used at the end of the command prompt line without a password, BCP uses the default password (null ).

-T

Specify that BCP uses the Security creden of network users to connect to SQL Server through a trusted connection. Login_id and password are not required.

-V

Report the version number and copyright of the BCP utility.

-R

Specify the region format defined for the region of the client computer to copy large data volumes of currency, date, and time to SQL Server. By default, the region settings are ignored.

-K

Specify that a null value should be retained for the hollow column in the large-capacity copy operation, instead of assigning the default value to the inserted column.

-E

The value of the specified ID column appears in the file to be imported. If-E is not provided, the ID value of this column in the data file being imported will be ignored, in addition, SQL Server 2000 automatically assigns a unique value based on the specified seed value and increment value during table creation. If the table or view in the data file does not contain the value of the ID column, the ID column in the table or view should be skipped when the data is imported using the format file; SQL Server 2000 automatically assigns a unique value to the column. For more information, see DBCC checkident.

-H "hint [,... n]"

Specifies the prompt used to copy data to a table or view in large capacity. This option cannot be used when data is copied to SQL Server 6.x or earlier in large capacity.

Description
Order (column [ASC | DESC] [,... n]) sorts the data in the data file. If the data to be loaded is sorted by the clustered index in the table, the performance of large-capacity replication is improved. If the data files are sorted in different order or the table does not have clustered indexes, the order prompt is ignored. The provided column name must be a valid column in the target table. By default, BCP assumes that data files are not sorted.
Rows_per_batch = number of data rows in each batch (that is, BB ). If-B is not specified, the entire data file is sent to the server as a single transaction. The server optimizes large-capacity loading based on the value BB. By default, rows_per_batch is unknown.
Kilobytes_per_batch = the approximate number of kilobytes (Kb) of data in each batch (that is, CC) of CC ). By default, kilobytes_per_batch is unknown.
Table-level locks are obtained during the tablock large-capacity copy operation. Because the lock control only reduces the competition for table locks during the large-capacity replication operation, this prompt can significantly improve performance. If the table has no index and tablock is specified, the table can be loaded by multiple clients at the same time. By default, the locking behavior is determined by the table option table lock on bulk load.
During the check_constraints large-capacity copy operation, all constraints on the target table are checked. By default, the constraints are ignored.
The fire_triggers parameter is specified with the in parameter. Any insert trigger defined in the target table will be executed during the large-capacity copy operation. If fire_triggers is not specified, the insert trigger is not executed. Fire_triggers is ignored for out, queryout, and format parameters.

Note
The values of the computed column or timestamp column in the data file to be imported are ignored. SQL Server 2000 automatically assigns values. If the data file does not contain the values of the calculation column or timestamp column in the table, you can use the format file to specify that the calculation column and timestamp column in the table should be skipped during data import; SQL Server automatically assigns a value to this column.

The calculation column and timestamp column will be copied from SQL server to a data file as usual.

The SQL Server identifier (including the database name, table name or view name, logon, and password) can contain characters such as embedded spaces and quotation marks. When you specify an identifier or file name that contains spaces or quotation marks at a command prompt, you must enclose the identifier in double quotation marks. In addition, you can specify the-Q option for the name of the owner, table, or view that contains embedded spaces or quotation marks, you can also enclose the name of the owner, table, or view in double quotation marks with square brackets.

For example, the northwind database has a table Jane's orders, which is owned by the user jane doe. To log on to the Jane Doe and password go DBA to copy the table from the northwind database to the orders.txt file in large capacity, run one of the following commands:

BCP "northwind. Jane Doe. Jane's orders" out "Jane's orders.txt"-C-Q-U "Jane Doe"-P "Go dba"

BCP "northwind. [Jane Doe]. [Jane's orders]" out "Jane's orders.txt"-c-u "Jane Doe"-P "Go dba"

To specify a database name that contains spaces or quotation marks, you must use the-Q option.