Fix/Recover Corrupted Sqlite3 Database Files

1. Do PRAGMA integrity_check or quick_check

C:\>sqlite3.exe corrupted_files.db
SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> pragma quick_check;
*** in database main ***
On page 2 at right child: invalid page number 256
Page 233 is never used
sqlite>

If you see "OK", then the database files would be good in theory.

2. Found SQLite Error, Dump whatever we could dump

代碼:


sqlite> .dump

or

代碼:


C:\>echo .dump | sqlite3.exe corrupted_files.db > corrupted_files.TXT

3. Review and Edit the TXT SQL files

(3.1) Edit & Review the SQL TXT to see if how many data you save back;
(3.2) At EOF, remove "ROOLBACK;", add "COMMIT;"
(3.3) Save the TXT file

代碼:


PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE `tttt` (`snoo` INTEGER PRIMARY KEY  NOT NULL  DEFAULT '', `ipad` INTEGER DEFAULT '', `urii` VARCHAR DEFAULT '', `date` DATETIME DEFAULT CURRENT_DATE);
INSERT INTO "tttt" VALUES(0,0,'http://vovo2000.com/paintbbs/','2010-08-22');
... 
... (...SNIP...)
...
INSERT INTO "tttt" VALUES(58863,0,'https://www.google.com.tw/','2012-08-07');
/**** ERROR: (11) database disk image is malformed *****/
/**** ERROR: (11) database disk image is malformed *****/
/*** REMOVE THIS LINE!!! ANALYZE sqlite_master; ***/
/*** REMOVE THIS LINE!!! ROLLBACK; -- due to errors ***/

/*** ADD COMMIT at last line ***/
COMMIT;

4. Backup and Load SQL TXT into Sqlite

(4.1) copy/cp corrupted_files.db __corrputed__backup.db
(4.2) delete/rm corrupted_files.db
(4.3) Load revised SQL TXT to re-init

代碼:


C:\>sqlite3 -init corrupted_files.TXT corrupted_files.db
-- Loading resources from corrupted_files.db

SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from tttt;
40095
sqlite> PRAGMA quick_check;
ok
sqlite>

(2,170 views)

"Fix Recover Corrupted Sqlite3 Database Files with Errors" 傳統頁面(電腦版)

首頁繪圖設計工作閒聊比賽活動美術討論標籤圖片

傳統桌面版 [ 登入/註冊 ]