Usage of BCP in Sybase

BCP is a tool provided by Sybase for database table-level data backup.

It is generally stored in the bin directory of the installed ASE or open client.

For ASE earlier than version 12, the BCP storage directory is $ Sybase/bin.

The directory to be stored after version 12 (including version 12) is $ Sybase/OCS-12_x/bin.

$ Sybase is the Sybase installation directory, and 12_x represents versions 12.0 and 12.5, which are displayed as 12_0 or 12_5.

The executable file name is BCP. EXE.

The parameter list is as follows: (available BCP-available)

Usage: BCP [[database_name.] owner.] table_name [: slice_number] {In | out} datafile

[-M maxerrors] [-F formatfile] [-e errfile]

[-F firstrow] [-l lastrow] [-B batchsize]

[-N] [-C] [-T field_terminator] [-r row_terminator]

[-U username] [-P Password] [-I interfaces_file] [-S server]

[-A display_charset] [-Q datafile_charset] [-Z language] [-v]

[-A packet size] [-J client character set]

[-T text or image size] [-E] [-G id_start_value] [-N] [-x]

[-M labelname labelvalue] [-labeled]

[-K keytab_file] [-r remote_server_principal]

[-V [security_options] [-Z security_mechanic] [-q]

Common Data Backup formats:

BCP dbname .. tablename out C:/temp/filename-USA-ppassword-sservername-C

You can.

-U indicates the Sybase logon name,-P indicates the Sybase logon password, and-s indicates the Sybase service name.-C indicates that the data is exported in visible text mode.

To recover data, replace out with in.

You can use the following method to generate an execution script that can export data from all tables in a database at a time.

Edit a file named bcpscript as follows:

Use dbname select the database to export data

Go

Select 'bcp dbname .. '+ name +' out C: /temp/'+ name +'-USA-p-ssybcdsrv-C' from sysobjects where type = 'U' in the sysobjects system table, the table with type U is the user table, the system table is S.

Go

Run the command in the following format:

ISQL-USA-ppassword-sservername-I bcpscript-O bcpout. bat

After the I parameter, It is the input file, and after the O parameter, the file is the output file obtained after the input file is executed.

After execution, you can get a batch file with the suffix BAT (a shell file is generated in UNIX and the corresponding execution permission is changed), which can be executed directly. That is, the corresponding data file is exported under the specified directory. The data of a table is a file. For example, in UNIX, the bat suffix is not required.

If you need to restore the data files backed up to the database again, you only need to replace the out parameter in the BCP command in the preceding operation steps with the in parameter.

Note: You are advised to perform some tests before performing operations on the official data.

In addition, you can use the-J charset parameter to modify the character set using BCP.

For more information about how to use tools such as BCP, see Sybase.

Perform the following steps for system transplantation:

1. Install the hardware environment of the new environment, including the network and hard disk status;

2. Install the operating system of the new environment, including the service pack;

3. Install sybase database products of the same version, including patches;

4. Add database users, devices, and other related information, which should be consistent with those in the old system.

5. Create a New System database;

6. use your table script or the DDL generation function in Sybase Central to export the table creation script in the old system to generate tables in the database. it is best to separate the table creation script from the script that creates table constraints (primary keys, foreign keys, and so on). First, do not create constraints on the table. After the data is imported, Add .;

7. BCP out data in the old system, according to the method mentioned above;

8. run scripts for creating other objects, including indexes, primary keys, foreign keys, stored procedures, triggers, and defaults;

9. modify the structure of the table to be modified in the new system or add a new table;

10. test whether the application system works properly.