Sybase database Easy Day-to-day maintenance

The day-to-day maintenance of the database is an important responsibility of the system administrator. Its content mainly includes the following parts:

First, backup system data

The Sybase system's backup and recovery mechanism guarantees the possibility of retrieving data when the system fails. SQL Server provides two different types of recovery mechanisms: one is the automatic recovery of the system, which is automatically performed every time the system is started, ensuring that transactions completed before the system is paralyzed are written to the database device, while unfinished transactions are rolled back, and the other is a manually completed recovery, which is done by DUMP and LOAD commands to perform manual backup and recovery work. Therefore, regular backup of the transaction log and database is a very important day-to-day maintenance work.

1, BACKUP database:

Each database should be unloaded after it is created to provide a mount basis. After this, the periodic table is scheduled to be discharged. such as unloading the database every Friday. It is recommended that you remove database cycles from a general database system once a week.

In addition to unloading the database on a scheduled basis, you will need to unload the database every time you run an operation that does not have a log. For example:

• The DUMP TRAN with No_log (because of the disk overflow of the database) is forced to run every time;

• Allow a select into/bulkcopy to make a quick copy each time you use sp_dboption, or create a permanent table with the SELECT INTO command, or use the WRITETEXT command.

The command to unload the database is:

DUMP DATABASE database_name
TO dump_device

database_name is the name of the database to be unloaded, Dump_device is the name of the device being unloaded. Using system procedure Sp_helpdevice can obtain the information of the equipment.

The following command is used to unload the database my_db:

DUMP DATABASE my_db
TO db_bk_dev

2. Backup transaction log:

If the transaction log is on the same device as the database, the transaction log should not be backed up separately from the database. This is the case with the master database and the user database less than 4M. Databases and logs for general database systems are placed on separate devices, so you can use the DUMP TRAN command to back up the logs separately.

The cycle of backing up the transaction log directly affects how much data is recovered, so it is recommended to back up daily.

The command format for backing up the transaction log is:

DUMP TRANsaction database_name

[To Dump_device]

[With truncate_only| With No_log| With No_truncate]

Where database_name is the name of the database to back up the transaction, Dump_device is the backup device name and can be backed up to the device only if the WITH TRUNCATE_ONLY or with NO_LOG clause is included.

Note: If you always use the dump datebase (backing up the database and its logs) without the dump TRAN, the transaction log will not refresh and become very large.

For master and small databases, you should run the dump TRANsaction command to refresh the log after each run of dump datebase.

The following command backs up the transaction log of the database db160 to the backup device:

DUMP TRANsaction db160
TO db_log_bk_dev
WITH TRUNCATE_ONLY

3. The interaction between the backup database and its log:

If a non-hardware failure occurs at Tuesday 5:01, all that is required is to mount tape 5 (see the next section: Data recovery), because tape 5 is just backed up by 5:00, so only the loss of data within one minute between backup and Mount.

But what if it expires at Tuesday 4:49? In this case, you want to mount the tape 1 (unloaded at Friday 5:00). Then, in turn, mount the tape 2,3 and 4. In this way, the system will revert to the state of Tuesday 10:00, most of the work in Tuesday was lost. This example shows the importance of frequently discharging transactions.