Sybase Database Backup and Restoration

Sybase database provides two types of database restoration functions: one is system automatic restoration and the other is manual restoration.

z System automatic restoration is a protective measure in case of system failure, the

system automatic restoration runs each time SQL Server reboots. System automatic restoration ensures that all the data are saved to the database devices before system failure, and those unfinished tasks will be deleted.

z The manual restoration is accomplished through loading database from other

places with command "dump" and "load". In Sybase database, backup is usually called "dump", and restore called "load" – by the names of these two commands.

Caution:

While carrying out database backup and restoration, the backup server must be running.

C.4.1 System Software Failure Restoration

System automatic restoration is a kind of protective measure accomplished by rebooting SQL Server in case of a system software failure.

The system will first try to restore the master database, then the model database, then the temporary database and finally the user database. The restoration mechnism will check the transaction logs in each database. If the transaction log has included the data newer than those in the database, the restoration mechnism will use those data and perform a roll-forward restoration, those unfinished transaction records will be deleted.

Note:

What is a transaction log

Every single change to the database is recorded in a system table, which is called a "transaction log". The transaction log records actions such as: update, insert and delete. All changes are logged before physical changes are made. The transaction log ensures the data can be restored to the status before system failure occurs.

C.4.2 Backup Database and its Transaction Log

Data backup is the most simple way of restoring important data lost as a result of database or table corruption, device failure or user mistakes. To keep the data as new

and updated as possible, data backup actions should be conducted as often as possible.

Data backup should be conducted when the system is relatively not so busy, this will ensure the integrity and accuracy of the data.

The following is the command format for data backup:

dump database database_name to filename | device

For example: to backup database "aaa" straight to system file "aaabak":

1> dump database aaa to aaabak 2> go

The following is the command format for transaction log backup (incremental backup):

dump transaction database_name to filename | device [with truncate_only | with

no_log | with no_truncate]

Parameter "with truncate_only" and "with no_log" have the same function – deleting transaction logs; parameter "with no_truncate" will keep and backup the whole transaction log (i.e. all that happened since last "dump transaction").

For example: to backup the transaction log of database "aaa" to system file "aaalog":

1> dump transaction aaa to aaalog with no_truncate 2> go

Caution:

z It is pointless to backup transaction logs before backing up database. Generally, it is not necessary to

backup database so often as transaction log, for it takes less space and time to backup transaction log than to backup database.

z Transaction log restoration should only be done after the backup of database is completed. When the

database is restored, user can start to restore one or more transaction logs. The transaction logs should be restored in correct order in which they are dumped. SQL Server will check the time stamp of each restored database and each transaction log for correct order.

C.4.3 Database Restoration

To restore the destroyed database, follow the steps below:

1) Use command "dump transaction" with parameter "no_truncate" to dump the user database transaction log.

3) Build a new database with the same structure as that of the destroyed database. (as for OMC Server database, OMC Server comes with a database rebuilding program, use it to rebuild the database instead of using command "build".) 4) Use "load database" to reload the database.

5) Use "load transaction" to reload the transaction log.

The following is the command format to reload the database:

load database database_name from file_name | device

The following is the command format to reload the transaction log:

load transaction database_name from file_name | device

For example: to restore database "aaa" with the database files and transaction log files:

Follow the steps below: 6) Restore database:

1> load database aaa from aaabak 2> go

7) Run command "online" to set the database in the normal working status after it is restored:

1> online database aaa 2> go

8) Restore transaction log:

1> load transaction aaa from aaalog 2> go

9) Delete transaction log:

1> dump transaction aaa with truncate_only 2> go

Caution:

z If command "dump transaction with no_truncate" is used alone too often, the transaction log will build

up as it is kept. To fix this, run "dump transaction with truncate_only" to delete the transaction log every time you run command "dump database" and "dump transaction with no_truncate".

z Command "load" will not work if the database is currently being used by other users. z It will take more time to restore a database than to backup it.

C.4.4 How to Monitor Transaction Log Space

I. Use system stored procedure "sp_helpdb"

For example: to monitor the transaction log space of database "warn":

1> sp_helpdb warn 2> go

II. Use system stored procedure "sp_spaceused"

The command format:

sp_spaceused tablename

For example: to query how much space table "history" takes up in database "warn":

1> use warn 2> go

1> sp_spaceused history 2> go

III. Use command "dbcc"

Refer to the following command format:

dbcc checktable (tablename)

For example: to query information about table "history":

1> dbcc checktable(history) 2> go

Checking history

The total number of data pages in this table is 4707. Table has 35437 data rows.

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Appendix D Maintenance of UNIX Operating