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

How to recover a corrupt SQLite3 database?

提问by linsek

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

这是先前回答的帖子的后续问题：是否有用于在 Linux 中验证 SQLite 数据库的命令行实用程序？

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

谢谢

回答by CL.

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

更新：现在有一个内置于 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:

有时，损坏仅或主要发生在索引中，在这种情况下，可以通过尝试使用 转储整个数据库来获取部分或大部分记录.dump，并使用这些命令创建新数据库：

$ 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.

最简单、最可靠的方法是从备份中恢复数据库文件。

回答by johnrechd

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

我有一个损坏的 sqlite 文件，它会显示这样的症状。

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.

它只会返回 7 条记录。

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).

在 Mac 上，打开终端并在损坏的数据库上运行这些命令。（这些是 sqlite3 命令，因此您应该能够在其他系统中使用其他 sqlite3 编辑器或类似命令）。

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/

这些步骤取自本网站：http://www.dosomethinghere.com/2013/02/20/fixing-the-sqlite-error-the-database-disk-image-is-malformed/

回答by arun

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

在Sqlite 3.29.0.recover中，CLI 引入了一个新命令：

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

添加“.recover”命令，该命令尝试从损坏的数据库文件中恢复尽可能多的内容。

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

回答by Stavr00

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

如果数据库严重损坏，.dump将包含错误，并且可能会丢失一些数据。

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

对于更复杂的数据模式，这意味着可能会混淆应用程序的孤立和/或部分记录。

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

它可能.dump比文件更可取，然后使用文本编辑器删除有问题的行。ERROR在转储文件中搜索。

回答by Oliver K?tter

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

我能够通过这种方式修复我的 Chrome 历史文件（它是一个 sqlite3 数据库文件）：

sqlite3.exe History ".backup History-new"

回答by Federico Firenze

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

回答by SuiTheDoc

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.

我知道这是一个老问题，但我仍然想分享我的解决方案。我的问题是 kodi(xbmc) 的 sqlite3 数据库已损坏。

.dump did not work in my case

.dump 在我的情况下不起作用

file is encrypted or is not a database

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 thissite 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 !!!

1. 备份了旧的 db 文件
2. 让 kodi 创建一个新的 db 文件
3. 在此站点上检查了 sqlite 文件的标头格式
4. 用十六进制编辑器打开这两个文件（祝福）并检查前 96 个字节
5. 前 40 个字节不同，所以我将前 40 个字节从新的 db 文件复制到旧的 db 文件
6. 这样做后，我的数据库文件又工作了！！！

回答by Thinkeye

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

pragmawritable_schema禁用了一些完整性检查，因此这两个命令也可能起作用，保持数据库自定义到位：

PRAGMA writable_schema=ON;
VACUUM;

回答by tmighty

This worked for me:

这对我有用：

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

从这里下载 sqlite3 工具包并将其放入任何文件夹。将损坏的数据库放在同一个文件夹中。

Open a command prompt.

打开命令提示符。

Type the following:

键入以下内容：

sqlite3.exe

sqlite3.exe

(Press Enter)

（按回车键）

NAME_OF_YOUR_CORRUPTED_DATABASE> ".dump" | sqlite3 new.db

NAME_OF_YOUR_CORRUPTED_DATABASE> ".dump" | sqlite3 new.db

(Press enter)

（按回车）

All the other solutions didn't work for me.

所有其他解决方案对我都不起作用。

回答by Mohit Chauhan

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

1. DROP Index：sqlite 删除索引命令

2. 运行真空Sqlite 真空命令

3. 再次重新创建索引：Sqlite 创建索引