Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Database corruption - great fix to share
northerner
Member Posts: 67
Hi all
I wanted to share a great database corruption fix. I'd love to take the credit for it, however the solution was provided by MS technical support and they deserve all credit.
A customer recently had a power cut, no UPS on the server, and the result was a corrupt NAV database (details: version 3.6, classic database, 45GB file size). We established that the corruption was in the primary key of a particular table, and were unable to access the data at all - via the table, through dataports, reports, codeunits, ODBC connections, anything we could think of. Also, there were other problems: 1. Whenever the database was restarted, the database used grew to 100% and the database had to be expanded before users could use it, and 2. It was not possible to take a backup due to the corruption.
The solution from Microsoft technical support was:
1. TRANSFERFIELDS all the records of table xxx to the brand new created table for ALL the companies
2. Delete all the records for table XXXX for all the companies except the one whom has got the corruption in the OLD database.
3. Perform a backup of all objects + data common to all companies + All the companies except the one with the table whom has got the error.
4. Create a brand new database with the proper space
5. Restore the backup of point 2 (now you just miss data of one company).
6. At the end of the restore, go to the Object Designer in the NEW database and DELETE table XXXX.
7. Go back to the OLD database. Go to the Object Designer and change the property of the table DataPerCompany to ‘No’. Save and Compile.
8. Perform a backup of ONLY the company that still remains (this won’t import the corrupted table).
9. Restore the backup at point 8. Into the NEW database (you should have all except the corrupted table).
In our case, there was only one company involved, so all I did was
1. Change the DataPerCompany property to No for the corrupt table
2. Take a full database backup, but deselect 'Data common to all companies'
3. Restore the database
4. Manually copy the Report List table (as this was the only other table in the database set to 'DataPerCompany' = No)
The end result was a fully working database, but without any data in the table that had the corruption. We were able restore data from a backup and manually recreate the rest. Had that not been the case, Microsoft would have been able to try their C/DART data recovery tool but thankfully we didn't need to go to that level.
Hope this is helpful if anybody else has a similar problem - it's a very simple but effective lifeline!
I wanted to share a great database corruption fix. I'd love to take the credit for it, however the solution was provided by MS technical support and they deserve all credit.
A customer recently had a power cut, no UPS on the server, and the result was a corrupt NAV database (details: version 3.6, classic database, 45GB file size). We established that the corruption was in the primary key of a particular table, and were unable to access the data at all - via the table, through dataports, reports, codeunits, ODBC connections, anything we could think of. Also, there were other problems: 1. Whenever the database was restarted, the database used grew to 100% and the database had to be expanded before users could use it, and 2. It was not possible to take a backup due to the corruption.
The solution from Microsoft technical support was:
1. TRANSFERFIELDS all the records of table xxx to the brand new created table for ALL the companies
2. Delete all the records for table XXXX for all the companies except the one whom has got the corruption in the OLD database.
3. Perform a backup of all objects + data common to all companies + All the companies except the one with the table whom has got the error.
4. Create a brand new database with the proper space
5. Restore the backup of point 2 (now you just miss data of one company).
6. At the end of the restore, go to the Object Designer in the NEW database and DELETE table XXXX.
7. Go back to the OLD database. Go to the Object Designer and change the property of the table DataPerCompany to ‘No’. Save and Compile.
8. Perform a backup of ONLY the company that still remains (this won’t import the corrupted table).
9. Restore the backup at point 8. Into the NEW database (you should have all except the corrupted table).
In our case, there was only one company involved, so all I did was
1. Change the DataPerCompany property to No for the corrupt table
2. Take a full database backup, but deselect 'Data common to all companies'
3. Restore the database
4. Manually copy the Report List table (as this was the only other table in the database set to 'DataPerCompany' = No)
The end result was a fully working database, but without any data in the table that had the corruption. We were able restore data from a backup and manually recreate the rest. Had that not been the case, Microsoft would have been able to try their C/DART data recovery tool but thankfully we didn't need to go to that level.
Hope this is helpful if anybody else has a similar problem - it's a very simple but effective lifeline!
0
Comments
but this works only if the table is not excessively related to other tables (like GL entry for example)...because you will have all the tables updated at the last moment before the crash, but the "bad" table updated only at the last backup time...
For example you can have a posted invoices without gl entries...anyway, it can be useful if you know what you're doing! :thumbsup:
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
I have done many of these repairs of corrupted databases. Never have I been able to use the exact same process twice. Every corruption is very different. Belias has indicated the first issue, that you may have a working database but it may not be consistent at the end, which is the advantage of C/DART in that the resulting DB will be consistent.
Also there are different procedures for primary key corruptions and secondary keys.
I would prefer if people thought of this post as a list of some very good options to try when recovering a database, but not a step by step solution to how to recover databases.
Either way, its good of you to post this and share with the community.