如何恢复损坏的 SQLite3数据库？

UPDATE: There is now an automatic method that is built into SQLite: .recover

Sometimes, the corruption is only or mostly in indexes, in which case it would be possible to get some or most records by trying to dump the entire database with .dump, and use those commands to create a new database:

$ sqlite3 mydata.db ".dump" | sqlite3 new.db

However, this is not always possible.

The easiest and most reliable way is to restore the database file from the backup.

If the database is seriously corrupt, the .dump will contain errors, and some data may be lost.

For more complex data schemas, this will mean orphaned and/or partial records which may confuse the application.

It may be preferable to .dump to a file, then use a text editor to remove problematic rows. Search for ERROR within the dump file.

I know this is an old question, but I would still like to share my solution. My problem was that a sqlite3 database for kodi(xbmc) was corrupted.

.dump did not work in my case

file is encrypted or is not a database

What worked was the following:

1. Made a backup of the old db File
2. Let kodi create a new db File
3. Checked on this site for the header format of sqlite files
4. Opened both files with a hex editor (bless) and checked the first 96 Bytes
5. The first 40 bytes where different so i copied the first 40 bytes from the new db file to the old db file
6. After doing this, my database file worked again !!!

I had an sqlite file that was corrupt that would show a symptom like this.

select count(*) from corruptTable;
return:38000;

But when I would try to load the records with

select * from corruptTable;

It would only return 7 records.

I tried several things, but these steps were the most successful.

On a mac, open terminal and run these commands on your corrupt database. (these are sqlite3 commands, so you should be able to use other sqlite3 editors or similar commands in other systems).

1 sqlite3 dbWithCorruptTable.sqlite (Obviously replace "dbWithCorruptTable" to your sqlite3 file that has the corrupt table)
2 .mode insert
3 .output dump_all.sql
4 .dump
5 .exit
6 Manually edit the dump_all.sql file in a text editor and remove the transaction statements. Usually there is a "BEGIN TRANSACTION" statement on the 2nd line of the file and a "ROLLBACK" statement on the last line. Remove these and save the file

These steps were taken from this website: http://www.dosomethinghere.com/2013/02/20/fixing-the-sqlite-error-the-database-disk-image-is-malformed/

I was able to repair my Chrome history file (which is a sqlite3 database file) this way:

sqlite3.exe History ".backup History-new"

This worked for me:

Download the sqlite3 tools package from here and put it into any folder. Put your corrupted database in the same folder.

Open a command prompt.

Type the following:

sqlite3.exe

(Press Enter)

NAME_OF_YOUR_CORRUPTED_DATABASE> ".dump" | sqlite3 new.db

(Press enter)

All the other solutions didn't work for me.

The following fix worked to repair my database without running any command line tools.

I got the "database disk image is malformed" error message when I was working with one of my tables so I ran [PRAGMA integrity_check] which returned

Main freelist: free-page count in header is too small

On tree page 16198 cell 1: 2nd reference to page 14190

Page 16988 is never used

Page 46637 is never used

row 4493 missing from index indexname1

row 4493 missing from index indexname2

row 4493 missing from index indexname3

I first saved the schema for the table with the bad indexes so I could recreate those indexes. I then dropped the indexname 1, 2, and 3 indexes with the [drop index _] command. I exported my tables one by one to JSON files and then truncated each table. Running the integrity check at that point was successful. I then added the three indexes back with the [create index _] command and imported each table's records from their JSON file export. Running the integrity check command is still returning "ok" with all of the records restored.

The pragma writable_schema disables some integrity checks, so this two commands might also do the trick, keeping db customizations in place:

PRAGMA writable_schema=ON;
VACUUM;

My method is similar, prevents a error rollback script:

sqlite3 database.db ".dump" | sed -e 's|^ROLLBACK;\( -- due to errors\)*$|COMMIT;|g' | sqlite3 database.new

I have fixed database corruption caused by missing indexes with these steps and they are working for me.

1. DROP Index: sqlite drop index command

2. Run vacuum Sqlite vacuum command

3. Recreate index again : Sqlite create index

With Sqlite 3.29.0 a new .recover command has been introduced to the CLI:

Add the ".recover" command which tries to recover as much content as possible from a corrupt database file.

sqlite3 broken.db ".recover" | sqlite3 new.db

I fixed it with the following steps after I even could not remove single corrupt rows by sql statement, only all.

1. Open the *.db with sqlite browser and PRAGMA integrity_check; indicates the corrupt table.
2. export this table as csv by sqlite browser
3. delete all in table. DELETE FROM [tablename]
4. Import csv with sqlite browser and select in import settings -> Advanced -> Conflict strategy -> Ignore Rows
5. After that my Integrity Check was OK

If the error from PRAGMA integrity_check is like "row NNN missing from index sqlite_autoindex_XXX", you can fix indexes with command REINDEX.

Open sqlite database in any db browser tool. Then issue following commands

vacuum;
REINDEX;

And save the changes. Mostly this will solve the error.