Recovering Corrupted SQLite Data: Saving .recover Output Correctly

Understanding the .recover Command and Output Handling in SQLite

Issue Overview: Failed Database Recovery via .recover and .save Commands

The core problem revolves around attempting to recover data from a corrupted SQLite database file (e.g., a Kobo e-reader database) using the .recover command in the SQLite CLI (Command-Line Interface). The user observed that executing .recover followed by .save did not persist the recovered data into a new database file. Instead, the output file (crpt2.sqlite) remained identical to the original corrupted database (crpt.sqlite). This occurs because the .recover command does not modify the in-memory database directly but instead generates SQL statements (INSERT commands) that must be executed to rebuild the database.

Key technical details:

• .recover Behavior: The .recover command scans the database file for recoverable data pages, constructs SQL statements to recreate tables and insert data, and writes these statements to standard output (stdout). It does not automatically execute these statements against the currently open database connection.
• .save Command Limitation: The .save command in SQLite CLI merely copies the current in-memory database state to a file. If the .recover output is not executed, the in-memory database remains unmodified, and .save writes an empty or unchanged database.
• File Deserialization Issues: Attempts to use --deserialize with .open failed because deserialization requires a valid SQLite database header, which was missing or corrupted in the original file.

The user’s confusion stemmed from a mismatch between expectations (.recover modifying the database directly) and reality (.recover generating SQL that must be redirected to a file and executed separately). The recovery process requires explicit handling of the generated SQL output to reconstruct a functional database.

Critical Missteps and Environmental Factors Leading to Failed Recovery

1. Interactive Shell vs. System Shell Context:
   The user executed .recover within SQLite’s interactive shell, where output redirection operators like > are not recognized. These operators are part of the system shell (e.g., Command Prompt on Windows), not SQLite’s CLI syntax. Attempting to use > inside SQLite’s interactive mode caused syntax errors (e.g., Parse error: near "crpt": syntax error).

2. Misunderstanding .recover’s Output Mechanism:
   The .recover command does not alter the database attached to the current SQLite session. Instead, it emits SQL statements to rebuild the database externally. Without capturing this output into a file and importing it into a new database, the original file remains unchanged.

3. Incorrect Use of .save After .recover:
   The .save command was invoked after .recover, but since .recover did not modify the in-memory database, .save merely copied the original corrupted database (opened via .open crpt.sqlite) into crpt2.sqlite.

4. Corruption Severity and Deserialization Failure:
   Using .open crpt.sqlite --deserialize failed because deserialization requires reading a serialized database schema, which was unavailable due to corruption. The .tables command succeeded without --deserialize because SQLite opened the database in "journal mode," ignoring some structural inconsistencies but leaving recovered data inaccessible.

5. Platform-Specific Command Syntax:
   The user initially attempted CLI commands formatted for Unix-like shells (e.g., corrupt.db .recover > recovered.db) within SQLite’s interactive shell on Windows, leading to syntax errors. The correct approach involves running the command from the system shell, not the SQLite prompt.

Comprehensive Recovery Workflow: Redirecting Output and Rebuilding the Database

Step 1: Generate Recovery SQL via System Shell

Exit SQLite’s interactive shell and execute the recovery command directly in the system shell (Command Prompt/Terminal):

sqlite3 crpt.sqlite ".recover" > recovered_data.sql

• Explanation:
  This command launches SQLite3, opens crpt.sqlite, runs .recover, and redirects the output to recovered_data.sql. The quotes around .recover ensure it is treated as a CLI command rather than an interactive input.

Step 2: Inspect and Modify the Generated SQL (If Needed)

Open recovered_data.sql in a text editor to verify its contents:

• Look for CREATE TABLE and INSERT statements.
• Ensure the lost_and_found table (or similar structures) contains the missing annotations.
• Remove any malformed SQL or duplicate entries if corruption introduced syntactic errors.

Step 3: Rebuild the Database from Recovery SQL

Create a new database and import the recovery SQL:

sqlite3 recovered.db < recovered_data.sql

• Explanation:
  This command initializes recovered.db and executes all SQL statements from recovered_data.sql, reconstructing tables and inserting recovered data.

Step 4: Validate the Recovered Database

Open recovered.db in SQLite and check for recovered data:

sqlite3 recovered.db
.tables
SELECT * FROM lost_and_found;

• Expected Outcome:
  The lost_and_found table should contain the missing highlights and annotations from the Kobo database.

Alternative Method: Using .once in SQLite Interactive Shell

If preferring to work within SQLite’s interactive shell:

.open crpt.sqlite
.once recovered_data.sql
.recover

• Explanation:
  The .once command directs the output of the next command (.recover) to recovered_data.sql. After generating the SQL, follow Steps 3–4 to rebuild the database.

Handling Edge Cases and Advanced Scenarios

• Partial Recovery: If .recover fails to extract all data, use third-party tools like sqlite3_recover (commercial) or sqlar (open-source) for deeper scans.
• Large Databases: Split recovered_data.sql into smaller chunks using text processing tools (e.g., split on Unix) to avoid memory issues during import.
• Schema Reconciliation: Manually merge recovered data into an existing schema using ATTACH DATABASE and INSERT INTO ... SELECT queries if the original schema is partially intact.

Preventing Future Corruption

• Enable Write-Ahead Logging (WAL):

  PRAGMA journal_mode=WAL;
  

• Regular Backups: Use .dump to create periodic SQL backups:

  sqlite3 kobo.db ".dump" > kobo_backup.sql
  

• Avoid Interruptions: Ensure the Kobo device is not unplugged during database writes.

By systematically redirecting .recover output and rebuilding the database, users can recover data even from severely corrupted files. The process emphasizes understanding SQLite’s separation of recovery (SQL generation) and reconstruction (SQL execution), which is critical for handling corruption scenarios effectively.