Recovery from a single, all inclusive log file

This document discusses the circumstances in which it is possible to perform a complete recovery from a log file and the steps required to perform such a recovery.  This recovery technique relies on the fact that the transaction log file is a forward transaction log which contains a record of all modifications made to the database including both Data Manipulation Language (DML) changes and Data Definition Language (DDL) changes. In other words, a transaction log can contain all the information necessary to recreate both the data and the underlying schema required for the database.

For more information on the transaction log, please refer to the following section of the SQL Anywhere Studio 8.0.0 documentation:

ASA Database Administration Guide
  11. Backup and Data Recovery
    Understanding backups

When should you refer to this document?

You should refer to this document when all of the following conditions are met:
 

• Your database does not participate in a SQL Remote or MobiLink system.
• Your database file is corrupt.
• You do not have a backup of the database file.
• You have a log file or files that record the entire life of the database you are trying to recover.
• There is no specific method for determining if your log file(s) satisfy this condition.
• The starting offset of the first log file for a given database will depend on the parameters specified when the database is initialized.
• For a 8.0.0.2289 database initialized with the default parameters, the starting log offset is approximately 0000343819.
• If the starting log offset of your oldest log file is significantly larger than this value, then your log file probably does not satisfy this condition.
• You can successfully translate the log file(s).

For more information on translating a log file, please refer to the following section of the SQL Anywhere Studio 8.0.0 documentation:

ASA Database Administration Guide
  15. Database Administration Utilities
    The Log Translation utility

If the above conditions are not met, then this document does not apply to your recovery situation. If conditions (3) and (4) are met, but conditions (2) and (3) are not met, then the process described in this document is feasible, however it is probably not the most efficient method possible to recover from your situation. If condition (1) is not met, then do not attempt a recovery using this method.

Recovery Steps

Initialize a new database with the same initialization parameters as the existing database. Most of the initialization parameters of the existing database can be determined using the Information utility.

For more details about the Information utility, please refer to the following section of the SQL Anywhere Studio 8.0.0 documentation:

ASA Database Administration Guide
  15. Database Administration Utilities
    The Information utility

Initialization parameters that can not be determined from the Information Utility include:

• jConnect Support
• JDK Version
• Java Support
• Existence of sys.syscolumns & sys.sysindexes views

To test for these characteristics, you can do the following:
 

Characteristic	Test	Interpretation
jConnect Support	SELECT * FROM sys.systableWHERE table_name LIKE ‘%jdbc%’	If any records are returned, then jConnect support was installed in the database.
JDK Version		5.5.x – No Java in the database  6.x    - JDK 1.1.6 7.x    - JDK 1.1.8 8.x     - SELECT db_property(‘JDKVersion’)
Java Support	SELECT * FROM sys.sysjar	The sys.sysjar table will be empty if Java classes are not installed
Existence of sys.syscolumns & sys.sysindexes views	SELECT * FROM sys.systableWHERE table_name IN (‘syscolumns’, ‘sysindexes’)	If any records are returned, then the –k switch was not used to initialize the database.

For instructions on how to initialize a new database, please refer to the following section of the SQL Anywhere Studio 8.0 documentation:

ASA Database Administration Guide
  15. Database Administration Utilities
    The Initialization utility

Translate the log file(s) using the Log Translation utility to generate SQL scripts.

Apply the SQL scripts to the new database, in the same chronological order that the corresponding log files were initially created, using the Interactive SQL utility.

For more information on the Interactive SQL utility, including examples of how to use it to apply a SQL script file, please refer to the following section of the documentation:

ASA Database Administration Guide
  15. Database Administration Utilities
    The Interactive SQL utility