Retrieve lost data

[kilt]

Hi everyone,
One of the tables in our database corrupted. It contains 4 data fields & has 40 records in it that have been added since our last backup. I would like to find the records that aren't in our backup so I can manually/ update the uncorrupted table. I know this can be done through comparing the two in a query, but I can't figure out how to just now! Please can anyone suggest how this can be done?

Thanks In Advance

Kilt

 

[scriverb]

Try this SQL in a new query:

Select A.ID as Record_NotBackedup 
FROM [red][i]yourtablename[/i][/red] as A LEFT JOIN [red][i]yourBACKUPtablename[/i][/red] as B on A.ID = B.ID
WHERE (B.ID) IsNull 
ORDER BY A.ID;

Should do the trick.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[kilt]

Bob,
I appreciate your help, unfortunately I'm not too hot on my SQL. I understand what the code is trying to achieve but when I write & try to save it I get an error of: Syntax error (missing operator) in query expression "(B.ID) IsNull". I've also noticed that some of the data fields in the table are also corrupted - particulary a yes / no field. Is there a way I can compare say row 1 tablename to row 1 backeduptable for any change to either of the 4 data fields? Or ideally any datafields from tablename compared to backeduptable. Any pointers or help to where I can read up on it would be great.

Cheers & TIA

Kilt

 

[scriverb]

Change the query to

WHERE (B.ID) Is Null

As for the corrupted data, sometimes it is better to just manually print out the new records from the end of the file and add them in. The 40 new records will be the last 40 added. You can identify them by adding an AutoNumber to the table if you don't already have one and changing the Primary key to the AutoNumber field. The last 40 at the bottom will be the new records. You should be able to highlight the records by selecting them on the left and performing a copy from the Edit menu. Then open up your backup table and paste them in at the bottom. Make sure you have an autonumber in the Backup table named the same as the corrupted table. Highlight the new record row and paste from the Edit menu. Should just paste them on the end.

Only problem would be if some of the corrupted records are in the 40 records.

The problem with comparing data fields is you will run into corrupted data fields and errors will occur. My experience is that the query shuts down when if finds a record it can't change.

But, yes a query can be written to compare one field to the other and updating the backup. If you want to try that after you get the 40 moved across just post back and we can give it a try.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[kilt]

Hi Bob,
I did try that last 40 method but it didn't work with my table. Of the 4 fields in the table, 2 constitute the Primary key with one of them being related in a one to many relationship with the main table of our database, which is an autonumber. That number is pulled out of the main table when needed & is thus generated in the required table in that order no. i.e if say Autonumber 10000 is used it will have all numbers >10000 below it & all < 10000 above it in the required table. Therefore taking out the Primary keys & putting in an autonumber in that table doesn't work. I hope I've explained that well!! I did use a query that pulled out 31 rows that weren't in the backup & have added them, so now there is a discrepancy of 9. The other problem is also in that some of the data fields that aren't Primary Keys seem to have changed. i.e. (A+B+C+D)backuptable = (A+B+E+F)Corrupttable. Only in a minority of cases. Can you suggest how to tackle finding out which rows have discrepancies? I apologise for the long winded explanation!!

Regards,

Kilt

 

[scriverb]

The relationship problem can be corrected. Make a full copy of both and put in an empty database togethter. NO RELATIONSHIPS. ADD the autonumber and change the primary keys. Now compare the final records in both and find where the new records start. Once you identify the records necessary to copy do the copying from the other tables. Corrupted to Backup preserving the indexing.

Give that a try.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[kilt]

Hi Bob,
I've taken both tables & did what you said. the data field Sort is the added autonumber

Here are the last entries from the corrupt table

Sort Tref Month Amount Sent
26480 12102 01/05/2004 £0.00 No
26481 12103 01/03/2004 £0.00 Yes
26482 12103 01/04/2004 -£7.50 No
26483 12103 01/05/2004 -£7.50 No
26484 12107 01/03/2004 £0.00 Yes
26485 12107 01/04/2004 £0.00 No
26486 12107 01/05/2004 -£7.50 No
26487 12111 01/04/2004 £0.00 Yes
26488 12111 01/05/2004 £0.00 No
26489 12134 01/03/2004 £0.00 Yes
26490 12134 01/04/2004 £0.00 No
26491 12134 01/05/2004 -£7.50 No
26492 10669 05/2004 £0.00 No
26493 10669 04/2004 £0.00 No
26494 8407 05/2004 £0.00 No
26495 8407 04/2004 £0.00 No
26496 9368 05/2004 £0.00 No
26497 9368 04/2004 £0.00 No
26498 12138 05/2004 £0.00 No
26499 12138 04/2004 £0.00 Yes
26500 12147 05/2004 £0.00 No
26501 12147 04/2004 £0.00 Yes

Heres the last entries for the backuptable
Sort Tref Month Amount Sent
26480 11411 01/05/2004 £0.00 No
26481 11423 01/04/2004 £0.00 Yes
26482 11423 01/05/2004 £0.00 No
26483 11847 01/04/2004 £0.00 Yes
26484 11847 01/05/2004 £0.00 No
26485 12054 01/04/2004 £0.00 Yes
26486 12054 01/05/2004 £0.00 No
26487 12138 04/2004 £0.00 Yes
26488 12138 05/2004 £0.00 No
26489 12147 04/2004 £0.00 Yes
26490 12147 05/2004 £0.00 No
26491 11933 01/04/2004 £0.00 Yes
26492 11933 01/05/2004 £0.00 No

As you can see the field Tref & subsequent data fields don't match the autonumber. The corrupt table only has 9 records more than the backup. Also some of the records for the amount & sent fields are different from the corresponding rows in the backup table. Any pointers? thanks for your time.

Regards,

Kilt

 

[scriverb]

I see that there is a problem. Are the tref and month two fields that uniquely identify your records? Are they the ones that you used in the query that I supplied to match the two tables together? If they are unique they you should have linked by a left join on both fields. This should identify all unduplicated records.

Post back with what you think.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]