Sybase Database Backup Scenario

Sybase database products Since its inception in 1987, its client/server (client/server) mode of operation, distributed processing of the basic architecture, perfect security and confidentiality performance, high-speed and efficient operation, multi-platform across the wide range of operating systems and other advantages, Quickly accepted and popularized by IT industry. As a user of the financial industry, we started using the Sybase10 version in 1995. In the specific use, it is found that the centralized data management has its advantages, that is, the scope of data risk is greatly reduced, but as Sybase's server end, the data risk is concentrated, how to do a good job of database data backup, become a crucial issue.

In the practical application, we have summed up two kinds of full scale backup scheme:

First, use Sybase's backup server (backup servers) to do a dump backup

Dump database, which is a physical backup of the entire database (including data, table structure, triggers, cursors, stored procedures, transaction logs, and so on). When the database is dumped, the system automatically executes a checkpoint, copying the logs and data from the buffer to the hard disk (only dirty pages), and dumps the allocated pages (logs and data) to the device.

The command format is:

Dump database database name to device file name with parameter

If you back up a file on your hard disk, you can not estimate the size of the backup file in advance, as long as your hard disk has enough storage space. If you are backing up to a DDS tape, you need to estimate the size of the backup, 90 meters of DDS tape compressed storage space for 4g,120 meters of tape compression storage space is 8G, if the database backup file exceeds this size, it is necessary to consider multiple file dumps, using stripe parameters.

The command format is:

Dump database name to device filename 1

stripe on device filename 2

stripe on device filename 3 ...

With parameter

This method of data backup is to complete the entire database of the operation of a full copy of the environment, includes dirty pages and fragments of the database, which can only be restored to the same size database (data and log sizes are only exactly the same as the original) when recovering using the load command, and is suitable for daily backups after work at the daily end of business outlets. The advantage is that the database is complete and correct, with the disadvantage that you cannot view the backup content directly.

Second, the data structure and records to do two times backup

In general, the database of the production machine is quite large, and the experimental machine is relatively small, it is difficult to restore the contents of the machine with the load command in the experimental environment. Therefore, in the work practice, we have worked out a second total amount of backup scheme.

1. Data structure Backup

Selecting Serveràgenerate DDL in the Sybase SQL Server Manager (database management) platform on the Windows platform produces all the information for the entire database server, if you choose Databaseàgenerate DDL produces the information of the selected database, taking the master library as an example, the resulting files mainly include: Config.ddl (Configuration), DEVICES.DDL (device), DATABASE.DDL (database), LOGINS.DDL (user), REMOTE.DDL (remote), CACHE.DDL (buffering), Segment.ddl (), GROUP.DDL (user group), USER.DDL (user), Default.ddl (True Province), Rule.ddl (rules), UDT.DDL, TABLE.DDL (table), VIEW.DDL (view), proc.ddl (stored procedure), TRIGER.DDL (triggers).

2. Data record Backup

Bulk the database tables into a format text file in the form of Sybase's bcp (copy) tool, command format:

BCP table name out pathname filename-c-t separator-u (username)-p (password)-s (server name)

If you make a bcp backup of all the tables in a database, you need to do a bcp for each table, and in general, a database has more than 100 tables and has a larger workload. You can use system table information from the database to make a bcp backup script. The principle is that each user table in the system table has information records, can be obtained through the isql statement query. Give an example to illustrate:

1. First edit a file called Mkbcpout.sql, which reads as follows:

select "echo bcp '"+name+" out ……………………'
"+"bcp "+name+" out ./+"name+".table -c -t '|' -Uuser -Ppassword -S SERVER" from sysobjects where type='U'
go

2, execute the isql command, with this file as input, execution results output to another file:

isql -Uuser -Ppassword -S server -I mkbcpout.sql -O bcpout

3), the Bcppout file slightly modified to increase the executable permissions

chmod +x bcpout

4), execute bcpout, export data text

./bcpout