Recover consolidated databases manually

The following procedure describes how to recover a consolidated database by applying each transaction log to the database. To have the SQL Anywhere database server automatically recover the consolidated database, see Recover consolidated databases automatically.

To recover the database using the -a option

Make a copy of the database and transaction log file. This procedure assumes that previous backups of the database file have been made and are available, for example on tape.
Create a temporary directory.
Restore the most recent back up of the database (.db) file, not the transaction log file, from tape into a temporary directory.

In the temporary directory:
1. Start the backup copy of the database.
2. Apply the old transaction logs using the -a option.
3. Shut down the database.
4. Start the database using the current transaction log and the -a option to apply the transactions and bring the database file up to date.
5. Shut down the database.
6. Back up the database.
Copy the database to the production directory.
Start the database.

Any new activity is appended to the current transaction log.

Example: Applying transaction logs individually

Suppose you have a consolidated database file named c:\dbdir\cons.db, a transaction log file c:\dbdir\cons.log, and a transaction log mirror file d:\mirdir\cons.mlg.

Assume that you perform full backups weekly, and you perform incremental backups daily using the following command:

dbbackup -c "UID=DBA;PWD=sql" -r -n -t e:\backdir

This command backs up the transaction log cons.log to the directory e:\backdir. The transaction log file is then renamed to datexx.log, where date is the current date and xx is the next set of letters in sequence, and a new transaction log is started. The directory e:\backdir is then backed up using a third-party utility.

In this scenario, you run the SQL Remote Message Agent (dbremote) with the optional directory to point to the renamed transaction log files. For example:

dbremote -c "UID=DBA;PWD=sql" c:\dbdir

On the third day following the weekly backup, the database file is corrupted because of a bad disk block.

Use the following procedure to recover from a media failure.

To recover from media failure on the C drive

Back up the transaction log mirror file d:\mirdir\cons.mlg.
Create a temporary directory to perform the recovery in. In this example, the directory is called c:\recover.
Restore the most recent backup of the database file, cons.db to c:\recover\cons.db.

Apply the renamed transaction logs in order, as follows:

dbeng12 -a c:\dbdir\dateAA.log c:\recover\cons.db
dbeng12 -a c:\dbdir\dateAB.log c:\recover\cons.db

Copy the current transaction log, d:\mirdir\cons.log to the recovery directory, giving c:\recover\cons.log.
Start the database using the following command:
dbeng12 c:\recover\cons.db
Shut down the database server.
Back up the recovered database and transaction log from c:\recover.
Copy the files from c:\recover to the appropriate production directories:
- Copy c:\recover\cons.db to c:\dbdir\cons.db.
- Copy c:\recover\cons.log to c:\dbdir\cons.log, and to d:\mirdir\cons.mlg.
Restart your system as normal.