How do I repair a corrupt database?
Article ID: 743 | Last Updated: Fri, May 12, 2017 at 10:08 AM
2Do uses the industry standard, SQLite embedded database to store your data. SQLite is lightweight, fast and can perform automatic recovery of failed or incomplete transactions under normal circumstances. In the rare event of a power-failure, sudden reboot or some other hardware related failure whilst in the middle of performing background sync, there is a slim chance of the database becoming corrupt, such that automatic recovery is no longer possible. This is more likely to occur on a Desktop / Laptop rather than an iPhone / Tablet. For phones and tablets, the easiest method of recovery, provided you have synced your data to the cloud, is to Reset your database from Settings > Advanced and setup sync to recover directly from the cloud.
Signs of a corrupt database are usually: search not working, sync not working or tasks not saving / deleting properly. Recovering from database failure is straightforward, albeit a manual process. The following instructions will help you self-diagnose a corrupt database and help you recover and repair your valued data. It should be noted here that 2Do creates automatic backups on a timely fashion (accessible from inside Preferences > Backups) for safe-keeping, however the following steps are there to help in case there isn’t a more recent backup to recover from.
- Spotlight for Terminal.app and launch it on your Mac
- Navigate to the db storage location by entering: cd ~/Library/Group\ Containers/EKT6323JY3.com.guidedways/
- Enter the following command and hit return: sqlite3 2do.db
The prompt should now display sqlite> to indicate you’re in the SQLite shell. To verify that you’re truly suffering from database corruption, enter the following command into the shell:
sqlite> PRAGMA integrity_check;
If the response is anything other than ok, the database is integrity checks have failed and needs to be repaired.
We need to now dump the data into an external file and re-create the database manually. Simply enter the following commands into the SQLite shell:
- sqlite> .mode insert
- sqlite> .output dump_all.sql
- sqlite> .dump
- sqlite> .exit
Now that we have the dump, let’s import it. You should now be out of the SQLite shell and back to the Terminal prompt. Execute the following statements:
- mv 2do.db 2do.orig.db
- sqlite3 2do.db < dump_all.sql
- sqlite3 2do.db
The first line above will move the original database out of the way. The second line will create a new one in place, using the dump we just exported. The last line should take us back into the SQLite shell in order to check if everything’s okay. Run the integrity check one more time and we should see OK as the response this time:
sqlite3> PRAGMA integrity_check;
ok
That’s it, you’re done repairing the database. Re-launch 2Do to get back to getting things done.
Print Article