Re: [sqlite] Repairing a Database.

[Kirk Clemons]

Thank you,
I believe this will help. Since my last email I took Rogers suggestion of 
looking over the structure of a database file and decided that if there is 
anything recovered from the .dump that it will need to be inserted into a 
database "template" in order to be functional.

The result was successful. I created a new database by importing the schema of 
a backup database that had not been corrupted like this;

.output Schema.txt
.schema

I then created a new database and set all of my user_version, journal_mode, 
etc. to match my original pre-corrupt specifications. Then I used .read to read 
in Schema.txt. This created an empty template database. Then I read in my dump 
file. The only errors were that the tables already exist but the insert into 
statements went through and my new database will launch in my application and I 
am able to recover the 3D symbols that were stored there and back them up.

It is not a 100% of course but it shows promise for future corrupt databases. I 
will test it on a few more of my client's databases and send an update of my 
findings.

Thank you all for your help,
~Kirk

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, August 05, 2010 4:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repairing a Database.


On 4 Aug 2010, at 10:15pm, Kirk Clemons wrote:

> Also, does this mean that I could take a backup copy of my database and 
> import the data that is retrieved from the .dump command on the corrupt 
> database? 

We cannot tell what .dump will get from your old database because it is 
corrupt.  It might miss out lots of records.  It might appear to be doing all 
the records but actually put the same values in each one.  It might dump the 
entire database perfectly.

> If so how would I do this and get past the PRIMARY KEY/existing table errors?

You can edit the file to replace 'INSERT' with 'INSERT OR IGNORE' or some 
variation on that.  See

<http://www.sqlite.org/lang_insert.html>

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repairing a Database.

[Simon Slavin]

On 4 Aug 2010, at 10:15pm, Kirk Clemons wrote:

> Also, does this mean that I could take a backup copy of my database and 
> import the data that is retrieved from the .dump command on the corrupt 
> database? 

We cannot tell what .dump will get from your old database because it is 
corrupt.  It might miss out lots of records.  It might appear to be doing all 
the records but actually put the same values in each one.  It might dump the 
entire database perfectly.

> If so how would I do this and get past the PRIMARY KEY/existing table errors?

You can edit the file to replace 'INSERT' with 'INSERT OR IGNORE' or some 
variation on that.  See



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repairing a Database.

[Kirk Clemons]

Also, does this mean that I could take a backup copy of my database and import 
the data that is retrieved from the .dump command on the corrupt database? 

If so how would I do this and get past the PRIMARY KEY/existing table errors?

~Kirk

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Roger Binns
Sent: Tuesday, August 03, 2010 5:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repairing a Database.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/02/2010 07:42 AM, Kirk Clemons wrote:
> I have seen some information on repairing a corrupt database and recovering 
> at least some of the data. But I have not had any luck finding out how to do 
> it.

You can use .dump in the shell.  It does a 'select *' on each table
outputting the rows (which will be in rowid order).  If it gets
SQLITE_CORRUPT then it repeats the select, but in reverse rowid order so in
theory you'll get the rows before and after the corruption.

In general you cannot repair or recover a corrupt database because to do so
would mean that information has to be stored redundantly - ie you could use
a duplicate to reconstruct the original.  The one exception is that indices
can be dropped and recreated since they are redundant.

The file format information will be most helpful.  The two documents
describe the same thing but with different styles.

  http://www.sqlite.org/fileformat.html
  http://www.sqlite.org/fileformat2.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxYup8ACgkQmOOfHg372QTYuwCg2GpSzG5qzltWQliyplKyPU2r
sTAAnROPV+qQcmhouUl9/z6RLDMnTMjm
=fvzY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repairing a Database.

[Kirk Clemons]

Thank you Roger,
I will look this over and see what I can do.

~Kirk

Kirk Clemons
Technical Support Analyst
Chief Architect(r)
6500 N. Mineral Dr.
Coeur d'Alene, Idaho 83815
Phone: (800)482-4433 
   (208)664-4204
 
Professional Software
www.chiefarchitect.com
 
Consumer Software
www.HomeDesignerSoftware.com
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Roger Binns
Sent: Tuesday, August 03, 2010 5:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repairing a Database.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/02/2010 07:42 AM, Kirk Clemons wrote:
> I have seen some information on repairing a corrupt database and recovering 
> at least some of the data. But I have not had any luck finding out how to do 
> it.

You can use .dump in the shell.  It does a 'select *' on each table
outputting the rows (which will be in rowid order).  If it gets
SQLITE_CORRUPT then it repeats the select, but in reverse rowid order so in
theory you'll get the rows before and after the corruption.

In general you cannot repair or recover a corrupt database because to do so
would mean that information has to be stored redundantly - ie you could use
a duplicate to reconstruct the original.  The one exception is that indices
can be dropped and recreated since they are redundant.

The file format information will be most helpful.  The two documents
describe the same thing but with different styles.

  http://www.sqlite.org/fileformat.html
  http://www.sqlite.org/fileformat2.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxYup8ACgkQmOOfHg372QTYuwCg2GpSzG5qzltWQliyplKyPU2r
sTAAnROPV+qQcmhouUl9/z6RLDMnTMjm
=fvzY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repairing a Database.

[Kirk Clemons]

Thanks for your reply.
I was afraid that may be the case. This is for my application that uses an
SQLite database to store 3D data so it would be difficult to find out which set 
of binary data is corrupt and which is not.

I do encourage my clients to backup the database, it is even 
stored in the 'My Documents' directory to make it easily accessible.
I also created a batch file executable that uses the 'sqlite3.exe' to perform 
the '.backup' command to create and compress a text version of the database. 
But I guess that's like trying to herd cats into a dog pound. ;)

Regards,
~Kirk.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Artur Reilin
Sent: Tuesday, August 03, 2010 7:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Repairing a Database.

If the database is corrupted, then this is really bad. If you don't have a
backup of your file, then it's almost not really possible to repair the
database. As far as I know...

You could open the database in an editor and try to pick some data, that
was left in the database.

with best wishes

Artur


> Hello All,
> I am new to SQLite and have run into an issue with a malformed database.
> It seems the application using the database was shutdown prematurely
> during an update operation and the journal file was never deleted. This
> means that on startup the application used the wrong journal file and
> corrupted the database. I have seen some information on repairing a
> corrupt database and recovering at least some of the data. But I have not
> had any luck finding out how to do it.
>
> Any help would be greatly appreciated.

> Kirk___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


Artur Reilin
sqlite.yuedream.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repairing a Database.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/02/2010 07:42 AM, Kirk Clemons wrote:
> I have seen some information on repairing a corrupt database and recovering 
> at least some of the data. But I have not had any luck finding out how to do 
> it.

You can use .dump in the shell.  It does a 'select *' on each table
outputting the rows (which will be in rowid order).  If it gets
SQLITE_CORRUPT then it repeats the select, but in reverse rowid order so in
theory you'll get the rows before and after the corruption.

In general you cannot repair or recover a corrupt database because to do so
would mean that information has to be stored redundantly - ie you could use
a duplicate to reconstruct the original.  The one exception is that indices
can be dropped and recreated since they are redundant.

The file format information will be most helpful.  The two documents
describe the same thing but with different styles.

  http://www.sqlite.org/fileformat.html
  http://www.sqlite.org/fileformat2.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxYup8ACgkQmOOfHg372QTYuwCg2GpSzG5qzltWQliyplKyPU2r
sTAAnROPV+qQcmhouUl9/z6RLDMnTMjm
=fvzY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repairing a Database.

[Artur Reilin]

If the database is corrupted, then this is really bad. If you don't have a
backup of your file, then it's almost not really possible to repair the
database. As far as I know...

You could open the database in an editor and try to pick some data, that
was left in the database.

with best wishes

Artur


> Hello All,
> I am new to SQLite and have run into an issue with a malformed database.
> It seems the application using the database was shutdown prematurely
> during an update operation and the journal file was never deleted. This
> means that on startup the application used the wrong journal file and
> corrupted the database. I have seen some information on repairing a
> corrupt database and recovering at least some of the data. But I have not
> had any luck finding out how to do it.
>
> Any help would be greatly appreciated.
>
> Kirk Clemons
> Technical Support Analyst
> Chief Architect(r)
> 6500 N. Mineral Dr.
> Coeur d'Alene, Idaho 83815
> Phone: (800)482-4433
>   (208)664-4204
>
> Professional Software
> www.chiefarchitect.com
>
> Consumer Software
> www.HomeDesignerSoftware.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


Artur Reilin
sqlite.yuedream.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users