User's Guide

Part III. Using SQL Anywhere

Chapter 25. Backup and Data Recovery

After a power failure or other system failure you should run the system disk verification program:

• For OS/2: Run scandisk with the /autofix option.
• For NetWare: Load the Novell VREPAIR NLM to repair any volume that will not mount due to errors.
• For QNX: Use chkfsys.
• For DOS or Windows NT, run the DOS or NT console command:

       chkdsk /f
  

  This command fixes simple errors in the file system structure that might have been caused by the system failure. This should be done before running any software, including Windows if applicable.

This fixes up simple errors in the file system structure that might have been caused by the system failure. This should be done before running any other software.

After a system error occurs, SQL Anywhere automatically recovers when you restart the database. The results of each transaction committed before the system error are intact. All changes by transactions that were not committed before the system failure are canceled. It is possible to recover uncommitted changes manually (see "Recovering uncommitted changes").

Steps to recover from a system failure

The database engine automatically takes three steps to recover from a system failure:

1. Restore all pages to the most recent checkpoint, using the checkpoint log.
2. Apply any changes made between the checkpoint and the system failure. These changes are in the transaction log.
3. Rollback all uncommitted transactions, using the rollback logs. There is a separate rollback log for every connection.

Frequent checkpoints make recovery from system failure take less time but also create work for the database engine writing out dirty pages.

There are two database options that allow you to control the frequency of checkpoints. CHECKPOINT_TIME will control the maximum desired time between checkpoints and RECOVERY_TIME will control the maximum desired time for recovery in the event of system failure (see "SET OPTION statement"). The RECOVERY_TIME specifies an estimate for steps 1 and 2 only.

Step 3 may take a long time if there are long uncommitted transactions that have already done a great deal of work before the last checkpoint.

The transaction log is optional. When you are running SQL Anywhere with no transaction log, a checkpoint is done whenever any transaction is committed. In the event of system failure, the database engine uses steps 1 and 3 from above to recover a database file. Step 2 is not necessary because there will be no committed transactions since the last checkpoint. This is, however, usually a slower way to run because of the frequent checkpoints.

Runtime engine is an exception The SQL Anywhere Runtime System does not employ a transaction log.

Quick recovery with a live backup

You can carry out a live backup of the transaction log by using the DBBACKUP command line utility with the -l command-line option.

Live backups provide a redundant copy of the transaction log that is available for restart of your system in case of system failure. A live backup runs continuously, terminating only if the server shuts down. If you suffer a system failure, the backed up transaction log can be used for a rapid restart of the system.