Recovery from media failure

Chapter 25. Backup and Data Recovery
Recovery from media failure

If you have backups, you can always recover all transactions that were committed before the media failure. Recovery from media failure requires you to keep the transaction log on a separate device from the database file. The information in the two files is redundant. Regular backups of the database file and the transaction log reduce the time required to recover from media failures.

The first step in recovering from a media failure is to clean up, reformat, or replace the device that failed.

The steps to take in recovery depend on whether the media failure is on the device holding your database file (see "Media failure on the database file") or on the device holding your transaction log (see "Media failure on the transaction log").

Media failure on the database file

When your transaction log is still usable, but you have lost your database file, the recovery process depends on whether you keep or delete the transaction log on incremental backup.

If you have a single transaction log

If you have not deleted or restarted the transaction log since the last full backup, the transaction log contains everything since the last backup. Recovery involves four steps:

Make a backup of the transaction log immediately. The database file is gone and the only record of the changes is in the transaction log.
Restore the most recent full backup (the database file).
Use the database engine with the apply transaction log ( -a ) switch, to apply the transaction log and bring the database up to date:
```
     DBENG50 sademo.db -a sademo.log
```
Start the database in the normal way. The database engine will come up normally and any new activity will be appended to the current transaction log.

If you have multiple transaction logs

If you have archived and deleted the transaction log since the last full backup, each transaction log since the full backup needs to be applied in sequence to bring the database up to date.

Make a backup of all transaction logs immediately. The database file is gone and the only record of the changes is in the transaction logs.
Restore the most recent full backup (the database file).
Starting with the first transaction log after the full backup, apply each archived transaction log by starting the database engine with the apply transaction log (-a) switch. For example, if the last full backup was on Sunday and the database file is lost during the day on Thursday.
```
     DBENG50W sademo.db -a mon.log
```
```
     DBENG50W sademo.db -a tue.log
```
```
     DBENG50W sademo.db -a wed.log
```
```
     DBENG50W sademo.db -a sample.log
```
SQL Anywhere does not allow you to apply the transaction logs in the wrong order or to skip a transaction log in the sequence.
Start the database in the normal way. The database engine will come up normally and any new activity will be appended to the current transaction log.

Media failure on the transaction log

When your database file is still usable but you have lost your transaction log, the recovery process is as follows:

Make a backup of the database file immediately. The transaction log is gone and the only record of the changes is in the database file.
Restart the database with the -f switch.
```
     DBENG50 sademo.db -f
```
Without the switch, the database engine will complain about the lack of a transaction log. With the switch, the database engine will restore the database to the most recent checkpoint and then roll back any transactions that were not committed at the time of the checkpoint. A new transaction log will be created.

Consequences of media failure on the transaction log

Media failure on the transaction log can have more serious consequences than media failure on the database file. When you lose the transaction log, all changes since the last checkpoint are lost. This will be a problem when you have a system failure and a media failure at the same time (such as if a power failure causes a head crash that damages the disk). Frequent checkpoints minimize the potential for lost data, but also create work for the database engine writing out dirty pages.

For running high-volume or extremely critical applications, you can protect against media failure on the transaction log by mirroring the transaction log or by using a special-purpose device, such as a storage device that mirrors the transaction log automatically. If you are using the SQL Anywhere Server for NetWare, NetWare allows you to automatically mirror a NetWare volume.

For information on using a transaction log mirror, see "Using a transaction log mirror".

Recovering uncommitted changes

The transaction log keeps a record of all changes made to the database. Even uncommitted changes are stored in the transaction log. The DBTRAN utility has a command line option (-a ) to translate transactions that were not committed. With this option, you can recover changes that were not committed by editing the SQL command file and picking out changes that you want to recover.

Changes not present in the transaction log
The transaction log may contain changes right up to the point where a failure occurred. It will contain any changes that were made before the most recent COMMIT by any transaction.