I have accidentally deleted a 76.4 GiB SQLite 3 DB file from an ext4 partition on an HDD. Thankfully, I immediately unmounted the partition and was able to recover the entire file using the photorec utility from testdisk.

Attempting recovery using sqlite3 command

$ sqlite3 coreprotect.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .recover
BEGIN;
PRAGMA writable_schema = on;
PRAGMA encoding = 'UTF-8';
PRAGMA page_size = '4096';
PRAGMA auto_vacuum = '0';
PRAGMA user_version = '0';
PRAGMA application_id = '0';
sql error: no such table: sqlite_dbpage (1)

sqlite> .schema
Error: database disk image is malformed

sqlite> .tables
Error: database disk image is malformed

sqlite> .databases
main: /mnt/data/restore/coreprotect.db r/w

sqlite> PRAGMA integrity_check;
Parse error: database disk image is malformed (11)

sqlite> PRAGMA quick_check;
Parse error: database disk image is malformed (11)

sqlite> .dump
sql error: database disk image is malformed (11)
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/****** CORRUPTION ERROR *******/
/****** database disk image is malformed ******/
/****** ERROR: near "ORDER": syntax error ******/
/**** ERROR: (11) database disk image is malformed *****/
COMMIT;

sqlite> .dump co_inventory
sql error: database disk image is malformed (11)
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/****** CORRUPTION ERROR *******/
/****** database disk image is malformed ******/
/****** ERROR: near "ORDER": syntax error ******/
/**** ERROR: (11) database disk image is malformed *****/
COMMIT;

sqlite> .dump co_container
sql error: database disk image is malformed (11)
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/****** CORRUPTION ERROR *******/
/****** database disk image is malformed ******/
/****** ERROR: near "ORDER": syntax error ******/
/**** ERROR: (11) database disk image is malformed *****/
COMMIT;

sqlite> .dump co_user
sql error: database disk image is malformed (11)
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
/****** CORRUPTION ERROR *******/
/****** database disk image is malformed ******/
/****** ERROR: near "ORDER": syntax error ******/
/**** ERROR: (11) database disk image is malformed *****/
COMMIT;

sqlite> SELECT * FROM co_chat LIMIT 1;
Parse error: database disk image is malformed (11)

sqlite> VACUUM;
Runtime error: database disk image is malformed (11)

So although I unmounted the partition immediately, it looks like some critical areas were overwritten.

Some possibly helpful info

$ file coreprotect.db
coreprotect.db: SQLite 3.x database, last written using SQLite version 3034000, file counter 47659247, database pages 20029972, cookie 0x3b, schema 4, UTF-8, version-valid-for 47659247

$ ./dump-sqlite-header coreprotect.db    # custom made program that dumps the db header
Reading from /mnt/data/restore/coreprotect.db
fileSize = 82042765312

headerBytes.size = 100

headerString = 'SQLite format 3.' (53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00)
pageSize = 4096
fileFormatWriteVersion = 1 (legacy)
fileFormatReadVersion = 1 (legacy)
unusedReservedSpaceEndPage = 0
maxEmbeddedPayloadFraction = 64
minEmbeddedPayloadFraction = 32
leafPayloadFraction = 32
fileChangeCounter = 47659247
databaseSizePages = 20029972, databaseSizeBytes = databaseSizePages * pageSize = 82042765312, fileSize - databaseSizeBytes = 0
firstFreelistTrunkPageNumber = 0
totalFreelistPages = 0
schemaCookie = 59 (0000003B)
schemaFormatNumber = 4
defaultPageCacheSize = 0
largestRootBTreePageNumber = 0
databaseTextEncoding = 1 (UTF-8)
userVersion = 0
incrementalVacuum = 0 (false)
applicationId = 0
reservedForExpansionBytes = 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 (all zero)
versionValidFor = 47659247
sqliteVersionNumber = 3034000 (3.34.0)

$ strings coreprotect.db | head -n20
SQLite format 3
kindexart_map_id_indexco_art_map
CREATE INDEX art_map_id_index ON co_art_map(id)w#
indexusername_log_uuid_indexco_username_log
CREATE INDEX username_log_uuid_index ON co_username_log(uuid,user)G"
]indexuuid_indexco_user
CREATE INDEX uuid_index ON co_user(uuid)G!
]indexuser_indexco_user
CREATE INDEX user_index ON co_user(user)Q 
qindexsign_indexco_sign
CREATE INDEX sign_index ON co_sign(wid,x,z,y,time)]
sindexsession_time_indexco_session
7qCREATE INDEX session_time_index ON co_session(time)b
}indexsession_user_indexco_session
7KCREATE INDEX session_user_index ON co_session(user,time)i
indexsession_action_indexco_session
7)CREATE INDEX session_action_index ON co_session(action,time)[
yindexsession_indexco_session
CREATE INDEX session_index ON co_session(wid,x,z,time)k
indexcontainer_type_indexco_container

The problem

Further analysis using hexdump -C or strings commands shows that a LOT of strings in the database are intact, so I'd assume there was damage to some critical areas of the structure.

The problem is, sqlite3 doesn't really tell what exactly is wrong here.

Any way to find what exactly is wrong here or anything I can try doing to make sqlite3 dump the data into a new .db file (or at least into a .csv or .sql)?

You could try the ".recovery" command:

https://sqlite.org/recovery.html#recovery_using_the_recover_command_in_the_cli

Dan.

There's no .recovery command. If you meant .recover, I've already tried that as I showed in the starting post, .recover was the first thing I tried.

Quoting the starting post:

$ sqlite3 coreprotect.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .recover
BEGIN;
PRAGMA writable_schema = on;
PRAGMA encoding = 'UTF-8';
PRAGMA page_size = '4096';
PRAGMA auto_vacuum = '0';
PRAGMA user_version = '0';
PRAGMA application_id = '0';
sql error: no such table: sqlite_dbpage (1)

The sqlite_dbpage virtual table may stop working if the schema of the main database is corrupt. I seem to recall sqlite3_rsync having a similar issue.

Suggested solution: add an optional schema parameter to the .recover command to make it use an attached database rather than main.

While I will wish you luck in recovery, the premise of the scenario stands out:

I have accidentally deleted a 76.4 GiB SQLite 3 DB file [..] was able to recover the entire file using the photorec utility from testdisk.

It is extremely unlikely that this file was stored on disk in a single, contiguous segment¹. If any file fragmentation existed (and it's very likely it did), photorec would not be able to recover the entire file. Photorec looks for file identifiers and tries to recover by "reading to the end", based on metadata in file formats. The apparent database corruption following suggests that SQLite is not finding data where it should be. You probably gathered up random bits of other files or unused space instead of making it all the way to the end of the complete file.

If you have not mounted the file system volume as read/write in the mean time, you may have better luck using a program like extundelete.

1. ^{^} This is particularly true if you never manually ran e4defrag on the database file, or were unaware of the on-disk state of the file (which can be revealed by filefrag).