Recovery from media failure

User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 20. 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 or on the device holding your transaction log.

Media failure on the database file

If your transaction log is still usable, but you have lost your database file, the recovery process depends on whether you 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 server with the apply transaction log (-a) switch, to apply the transaction log and bring the database up to date:
```
dbeng6 asademo.db -a asademo.log
```
Start the database in the normal way. The server 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.

To restart from a backup, with multiple transaction logs:

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 server 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.
```
dbeng6 asademo.db -a sun.log
dbeng6 asademo.db -a mon.log
dbeng6 asademo.db -a tue.log
dbeng6 asademo.db -a wed.log
dbeng6 asademo.db -a sample.log
```
Do not apply the transaction logs in the wrong order or skip a transaction log in the sequence.
Start the database in the normal way. The server will come up normally, and any new activity will be appended to the current transaction log.

Media failure on the transaction log

If 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.
```
dbeng6 asademo.db -f
```
Without the switch, the server will complain about the lack of a transaction log. With the switch, the server 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. If hen you lose the transaction log, all changes since the last checkpoint are lost. This will be a problem if 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 server 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 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.

The transaction log may or may not contain changes right up to the point where a failure occurred. It certainly contains any changes that were made before the most recent COMMIT by any transaction.