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,'https://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>
________________
美術插畫設計案子報價系統 v0.1 Beta
爪哇禾雀
|
