Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Database Administration » Backup and Data Recovery » Backup and recovery tasks

Recovering uncommitted operations Next Page

Recovering from multiple transaction logs


SQL Anywhere allows transactions to span multiple transaction log files. If a database is backed up part way through a transaction, the transaction may span two transaction log files. When this occurs, the first part of the transaction is contained in the offline transaction log, while the second part of the transaction is contained in the online transaction log.

If you need to recover your database and you have multiple transaction logs, you must apply the transaction log files to the backup copy of your database in the correct order in case there are transactions that span multiple transaction logs. If the transaction logs are not applied in the correct order, then portions of transactions that span multiple transaction logs are rolled back.

You can use any of the following methods to apply transaction logs in the correct order:

Recovering from multiple transaction logs using the -a server option

The -ad server option is used to recover a database by applying all the transaction logs from a specified directory to the backup copy of a database. When this option is specified, the database server applies the log and then shuts down the database.

To recover from multiple transaction logs using the -ad server option
Example

The following example applies the offline (backup) and current transaction logs to the backup copy of the sample database using the -a database server option.

  1. Start the database server and apply a backup transaction log called backupdemo.log to the backup copy of a database called backupdemo.db:

    dbeng10 backupdemo.db -a backupdemo.log

    The database server applies the backup transaction log to the backup copy of the database and then shuts down.

  2. Start the database server and apply the current transaction log called demo.log to the backup copy of the database:

    dbeng10 backupdemo.db -a demo.log

    The database server applies the current transaction log to the backup copy of the database and then shuts down.

Recovering from multiple transaction logs using the dbtran utility

To maintain the integrity of your data when you use dbtran to translate multiple transaction logs, you must specify both the -m and -n options. The -m option instructs the Log Translation utility to generate a file (named by -n) containing all the transactions from the logs in the specified directory.

You need to use -m because if you translate each log individually using dbtran, any transactions that span transaction log files could be rolled back. This is because when dbtran translates a log, it adds a ROLLBACK statement to the end of the log to undo any uncommitted transactions. In cases where a transaction spans two logs, the COMMIT for the transaction occurs in the second log file. Operations at the end of the first log file would be rolled back by dbtran because the file does not contain a COMMIT for the transaction. Translating all the transaction log files in a directory using -m ensures that all your transactions are translated. See Transaction Log utility (dblog).

To recover from multiple transaction logs using the dbtran utility
  1. Run the Log Translation utility (dbtran) against the directory containing the transaction log files and output the resulting SQL statements into a .sql file.

  2. Start the backup copy of your database.

  3. Apply the .sql file generated by dbtran in step 1 to the backup copy of your database from Interactive SQL.

Example

The following example uses the dbtran utility to apply the backup and current transaction logs to the backup copy of the database.

  1. Run the Log Translation utility against the c:|backup directory and output the SQL statements into a file called recoverylog.sql:

    dbtran -m "c:\backup" -n recoverylog.sql
  2. Start the backup copy of the database called backupdemo.db:

    dbeng10 backupdemo.db
  3. Apply the recoverylog.sql file to the database from Interactive SQL:

    dbisql "UID=DBA;PWD=sql;END=backupdemo" READ recoverylog.sql