How to recover a corrupt SQLite3 database?

Question

This is a follow up question to a previously answered post: Is there a command line utility for validating SQLite databases in Linux?

If a database is producing the following error:

$ sqlite3 mydata.db "PRAGMA integrity_check"
Error: database disk image is malformed

Is there any cleanup processing that can be done to recover the database to a usable state? Even at the potential loss of some corrupt records?

Thanks

score 157 · Accepted Answer · edited Apr 09 '20 at 05:03

157

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.

edited Apr 09 '20 at 05:03

Andy Joiner

5,932
3
45
72

answered Aug 15 '13 at 19:52

CL.

173,858
17
217
259

3

This actually worked for me. ".dump" produced acceptable SQL from my corrupt file that integrity_check was failing on. – BrandonLWhite Nov 26 '14 at 17:15
3

Huge thanks for your reply! Fixed my database in minutes ;) It is, however, important to note, that all entries that contain malformed data will simply not be copied, and the new database will contain all entries that could be recovered from the old database. Just take a look at the output of .dump, you'll see how your database works and which entries produce errors ;) – LukeLR Sep 10 '15 at 15:21
I just gave this a try with a similar issue and it gave me an empty file and no feedback. Without the "| sqlite3 new.db" part it gives me feedback, but no file. Help anyone? – Ne Mo Jun 04 '17 at 12:16
@NeMo Read the last two sentences of the answer. – CL. Jun 04 '17 at 14:58
As it happens, I did make a backup, but for unknown reasons it didn't work properly and didn't back up over half of what I told it to... – Ne Mo Jun 04 '17 at 19:17
1

@NeMo I also got an empty db but then when I looked at the script the last line was ROLLBACK; I removed this and changed it to COMMIT; which worked – RobbZ Aug 15 '19 at 21:49
@RobbZ, your comment brings me hope! I sitll have the file but it's over two years since I grappled with it. You're saying 'script', but this answer suggests a command, not a script. Where exactly is this line with ROLLBACK in it? Is it part of the database file itself (I don't have it to hand)? Sorry if that sounds naive/ignorant, but this isn't something I know a whole lot about! Thanks – Ne Mo Aug 16 '19 at 09:57
3

@NeMo The command as written by CL is 2 parts. The first part creates a SQL script of the entire DB (.dump). The second half writes it to a new db. But because it is piping the output of one command to the other you never get an actual file. If you pipe the output of the first command to a file you will be able to edit it. So if you instead run `sqlite3 mydata.db ".dump" > db_script.sql` you will get a SQL script you can edit and make sure there is nothing strange at the end like ROLLBACK; Then you can create a database from that script using `sqlite3 recovered.db < db_script.sql` – RobbZ Aug 16 '19 at 11:41
I was deploying code using latest sqlite3 libs to EC2, which for reasons best known to Amazon, is stuck on sqlite3 version 3.7.17, and would not update to latest 3.37.0 (at time of writing). This gave all the appearance of a corrupt database. Many simple `select` queries answered `table not found`. I updated sqlite3 and the corruption all went away. – Ben Aug 05 '22 at 18:30

score 59 · Answer 2 · edited Feb 21 '20 at 17:23

59

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

edited Feb 21 '20 at 17:23

mwfearnley

3,303
2
34
35

answered Sep 10 '19 at 13:47

Arundale Ramanathan

1,781
1
18
25

The command does not work, it shows "SQL error: database disk image is malformed" .. i know that its corrupted, im trying to recover – SubqueryCrunch Sep 23 '19 at 08:45
@SubqueryCrunch I shared what I came across. I hope SQLite guys will see your message. I have come across this "malformed" error when my first page itself was corrupted. – Arundale Ramanathan Sep 23 '19 at 12:02
4

This worked for me (though I ran out of memory). I used `sqlite3 broken.db ".recover" | sqlite3 new.db` – Luca Dec 12 '19 at 13:42
2

".recover" worked on a >1Gb database for me. It only took 1min to recover the database so It is more efficient than any commercial SQLite recovery tools I tried before (some of which where not even able to recover the database while others took nearly an hour to do it). So big thanks to the SQLite developper team for this really usefull feature – Thomas Bernard Jan 15 '20 at 12:36
I tried this. Error messages: "Error: near line 128760: too many columns on lost_and_found" followed by many lines of "Error: near line 123456: no such table: lost_and_found". Not sure what's going on here... – Frank van Wensveen Jun 15 '21 at 10:42

johnrechd · Answer 3 · 2015-05-20T18:38:57.010

25

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/

edited May 20 '15 at 18:38

answered May 20 '15 at 18:30

johnrechd

1,801
19
25

5

Note: when the file is very **big** (like 500Mb) you can't easily edit the SQL file. you should then use a command line this : --> cat dump_all_last.sql|grep -v TRANSACTION|grep -v ROLLBACK >dump_all_last_notrans.sql – Nadir Sep 11 '15 at 02:18
1

Good Point Nadir, thanks for adding to this with your comment. – johnrechd Sep 11 '15 at 15:19
Do you have any idea how to get this into an Automator app? – callisto Apr 28 '16 at 10:38
1

This approach may be good for a small database, but on a 8.5GB database it produces about 15 GB of SQL code. Even the Visual Studio editor can't open this file for editing on my machine. – Mar Oct 11 '16 at 06:54
Just a suggestion for those that have a DB that is too big to read into an editor. Just write a simple file parsing program that reads the file and looks for the transaction statements and removes them (or writes each line to a new file and excludes those). Shouldn't be the worst thing to implement. – johnrechd Sep 10 '19 at 16:48

score 16 · Answer 4 · edited Sep 23 '19 at 10:30

16

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

edited Sep 23 '19 at 10:30

Peter Lamberg

8,151
3
55
69

answered May 09 '19 at 22:23

Federico Firenze

300
2
6

2

This was the only method that allowed me to repair the scheme of my nextcloud sqlite db, so I could restore a backup (which requires the main script to run, which early schema errors will prevent). – TauPan Oct 21 '19 at 17:03
1

This worked me to restore the history of the AWS CLI which, due to the corrupt history db constantly errors out with `database disk image is malformed`. – Ashutosh Jindal Nov 21 '19 at 16:15

score 12 · Answer 5 · answered Oct 24 '14 at 13:58

12

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.

answered Oct 24 '14 at 13:58

Stavr00

3,219
1
16
28

1

Passing the dump directly to sqlite3 was not working alone, removing error lines in temporary dump fixed it. – Alexandre Lavoie Aug 31 '15 at 18:51

score 9 · Answer 6 · answered Jan 12 '17 at 08:02

9

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

sqlite3.exe History ".backup History-new"

answered Jan 12 '17 at 08:02

Oliver Kötter

1,006
11
29

3

This is nice! I've always used .dump and then loaded to a new database. Your method has the upside that it works with databases that use custom collations etc things that don't with .dump. – mz2 Feb 20 '17 at 16:53

score 7 · Answer 7 · edited Apr 25 '19 at 17:51

7

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:

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

edited Apr 25 '19 at 17:51

Tyler Marshall

489
3
9

answered Apr 04 '15 at 12:20

SuiTheDoc

179
2
9

2

Nice one. After accomplishing this, you should probably immediately rebuild the database, .e.g. using `.dump`/`.repair` as in other answers, to make sure the file has complete integrity. – mwfearnley Feb 21 '20 at 16:31

score 6 · Answer 8 · answered Jan 07 '19 at 14:24

6

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;

answered Jan 07 '19 at 14:24

Thinkeye

888
12
22

1

This allowed me to recover a very corrupted database! Cheers! – SubqueryCrunch Sep 23 '19 at 09:27

score 4 · Answer 9 · answered Jun 03 '19 at 11:47

4

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

DROP Index: sqlite drop index command
Run vacuum Sqlite vacuum command
Recreate index again : Sqlite create index

answered Jun 03 '19 at 11:47

Mohit

634
5
15

score 3 · Answer 10 · answered May 27 '17 at 15:47

3

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.

answered May 27 '17 at 15:47

tmighty

10,734
21
104
218

1

This looks like an interactive version of the accepted answer? (Albeit obviously Windows-specific..) – mwfearnley Feb 21 '20 at 16:34

score 2 · Answer 11 · edited Dec 11 '19 at 12:07

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.

score 2 · Answer 12 · answered Nov 02 '20 at 13:38

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

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

score 2 · Answer 13 · answered Jun 01 '21 at 05:58

2

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

answered Jun 01 '21 at 05:58

VLL

9,634
1
29
54

score 2 · Answer 14 · answered Oct 24 '22 at 16:27

2

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

vacuum;
REINDEX;

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

answered Oct 24 '22 at 16:27

Fathah Rehman P

8,401
4
40
42

How to recover a corrupt SQLite3 database?

14 Answers14

Linked

Related