NAV Team: Multiple Record Corruption on a Native Database: The “Table Isolation” Trick

[Blog bot]

Due to unexpected events, a multiple record corruption at table level in a Native database may unfortunately happen. The error that may arise is something like this (reported below in Danish) while performing actions on determined records in the corrupted area.



This error text is taken directly from the fin.stx file. For example:

…

// ÚÄ´ DB ÃÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ¿

// ³ ÀÄÄÄÄÙ ³

// ³ Database System ³

// ÀÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÙ

@1@19@0@

@2@1@0@

Caution: There is an error in the database structure. This occurs if the database file is changed by another program or if a device driver does not function properly.

You must correct this error before you can continue. The error (%18 in module %23) may have been caused by the computer or a program.

Table: %1.1

Company: %1.9

Key Fields: %6

Check the database. On the File menu, click Database, click Test, and then click Maximum. If you get the same error, you should:

1. Back up the database by copying the database file or files to another location.
2. Use the built-in functionality to make a backup.
3. If this works, create a new database without deleting the database that contains the error or errors.

Restore the backup that you made with the built-in backup functionality into the new database. To test it, on the File menu, click Database, click Test, and then click Maximum.

If this procedure does not work, then any database changes that were made after the most recent backup will be lost. Restore the most recent backup that was made with the built-in backup functionality and test it.

For security reasons, you should save the old database until you have used the new one for a period of time.

Contact your system administrator for assistance.

…

The official position from Microsoft, then, is to restore a valid recent backup or a copy of the database not affected from this error. But what if you do not have any of those?

There are some chances to isolate those corrupted records, like cancer cells, and create a brand new database with only the sane part of the old one. I have called this “Table Isolation” trick, observing it from a medical perspective.

This action plan could be applied if:

1. You can access safely the database and work in most of the area.
2. You know exactly which table(s) is/are affected and those are in a limited number.
3. Those tables have the table property DataPerCompany set to Yes.

If those 3 prerequisites can be considered fulfilled then you could go with the table isolation trick.

NOTE: In the next example we take into considerations the corruption of multiple records within table 37 Sales Line for one specific company.


“TABLE ISOLATION” plan

1. Create a brand new table XXXX that has got the same table structure as the corrupted table e.g. 37 Sales Line table. (This brand new table must have the same fields and data types, there should even be no need to copy C/AL Code and triggers on it.)
2. TRANSFERFIELDS ALL the records of Sales line table to the brand new created XXXX table for ALL the companies. (For the Sales Line records of the company that are having this problem, you should go with trial and error to recover as much sane records as possible.)
3. Delete ALL the records for the Sales Line table for ALL the companies EXCEPT the one that has got the corruption in the OLD database.
4. Perform a NAV backup of all objects + data common to all companies + ALL the companies EXCEPT the one with the table whom has got the error.
5. Create a brand new database with the appropriate space (we can call it NEW database).
6. Restore the NAV backup at point 2. (Now, in the NEW database you just miss data of one company.)
7. After the NAV restore, TRANSFERFIELDS back all the records from table XXXX to Sales Line for every company and after that DELETE all records in table XXXX.
8. Go back to the OLD database. Go to the Object Designer and change the property of the Sales Line table DataPerCompany to ‘No’. Save and Compile the table object.
9. Perform a NAV backup of ONLY the company that still remains. (From the operation done at point 8, this won’t import the corrupted table).
10. Restore the NAV backup at point 8 into the NEW database. (You should have now in the NEW database, all except the corrupted table. But the sane records are still in table XXXX.)
11. TRANSFERFIELDS back all the records that you have been able to save into table XXXX to Sales Line of this last company restored and then DELETE all records present in table XXXX.
12. DELETE table n. XXXX.

At the end of this procedure, remember to run a FULL NAV database test and correct all the error that may arise.

Remember also to correct all the dependencies left by the missing record and, overall, PLEASE before do this in live environment, always TEST it on a safe test environment.

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Best Regards,

Duilio Tacconi (dtacconi)

Microsoft Dynamics Italy

Microsoft Customer Service and Support (CSS) EMEA



Источник: http://feedproxy.google.com/~r/Micro...ion-trick.aspx