Check and repair a backup or archive index in P5
Posted by Sven Koester, Last modified by Andre Kuehnemund on 07 December 2017 16:34
This article is intended for cases when an error „database is malformed“ appears in P5. It is about checking and repairing structural index errors. Note that only the index's structural integrity is handled here, not its content or actuality.
Checking an index
P5 backup indexes consist of four *.db files, archive indexes have two more files. These reside in a subfolder of the P5 install directory:
The files in backup indexes are
Archive index have two further files.
These *.db files can be checked and repaired.
Before starting please always stop P5 to avoid P5 accessing the index in parallel.
To check which or whether a file is broken, the sqlite3 terminal can be used. In the unix variants of P5, the program is included. The Windows version can be downloaded from http://sqlite.org.
The check is done in a terminal window like this:
sqlite> pragma integrity_check(10);
So this file is checked ok. A broken file looks like this:
Repairing the index (the standard way)
The safest way to repair an index is by recovering an intact version from tape. For a broken backup index that can be achieved by navigating to the Advanced Options → Manage Indexes section, selecting the index and clicking „Check“. The check function will check the index and recover the last saved version from tape. For archive indexes, there is no automatic backup, so hopefully you saved the index yourself.
In case the problem persists
In case the last backup is too old by any reason, or in case that does not overcome the problem and the saved index was already faulty, again the sqlite3 command can be used to dump the index and then create a new index from tape. This command creates a dump of the database and directly reads that dump with another instance of slite3 that creates a new database from the dump:
echo .dump | /usr/local/aw/bin/sqlite3 addrs.db | /usr/local/aw/bin/sqlite3 addrs_new.db
That way a new file is written, here addrs_new.db. Please check whether the new file has a similar size as the original. The size will probably slightly smaller, as the internal structures are recreated in a more optimized way , but care must be taken that the mechanism did not fail completely by any reason and bigger parts of the data are lost.
In case the size fits, replace the dumped file with the new one, that new file should then be ok.