How can I verify that a SQLite db3 file is valid/consistent

Question

I have some sqlite version3 db3 files I copied off a live running production system (I know bad sysadmin bad sysadmin) for various reasons. Is there some sqlite command I can run that will verify that all the data can be read out of these files (I don't mind if it takes a while).

I was considering hacking up some perl which dumps out all data and then re-imports it into new files. I think sqlite will throw an exception if it encounters corrupt data. Is there a better way?

I'm CentOS 5.3 and sqlite-3.3.6-2

"bad sysadmin bad sysadmin"? What's that? – John Oct 09 '21 at 11:22 — John, Oct 09 '21 at 11:22

score 44 · Accepted Answer · edited Jan 15 '15 at 13:52

44

I think you want to try:

pragma integrity_check;

From the documentation:

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. [...]

See also the PRAGMA quick_check command which does most of the checking of PRAGMA integrity_check but runs much faster.

edited Jan 15 '15 at 13:52

Cristian Ciupitu

6,396
2
42
56

answered May 14 '09 at 01:41

hernan43

906
8
6

3

See also http://www.sqlite.org/faq.html#q21 – Teddy Jul 09 '09 at 10:36
Doing this for a gnucash database 13M in size. It returned Ok almost instantly. Seemed too quick to actually check anything, but maybe that database is small potatoes. Gnucash is crashing, which is why I am checking. – Stuart Gathman May 11 '23 at 00:14

How can I verify that a SQLite db3 file is valid/consistent

1 Answers1