NAV 4.0 SP3 Errors in database - how to backup in read-only

emulsified

Recently our Navision 4.0 SP3 NATIVE server (whole domain controller) experienced a mainboard crash. Backup files were stored on an attached array and some of them I found out were corrupted as well.

The last backup that successfully restores is from 3/11/2012.

I have a few questions:

I can open the crashed database from 3/29/2012, it is about 21gb in size. It opens and says "recovering list of free blocks" then I get an error message stating there are errors in the database. When I look at the free space it shows about 30% free space in the database.

When I close the database and open it again it shows 100% used. I tried expanding it and it just keeps consuming any amount of space I add to it.

When I attempt to do a backup it starts then stops around G/L Entry with the error message that there are errors in the database and promptly stops.

I have read a lot in the past 12 hours and tried many things as I have multiple copies of the database file made since the crash to test what will hopefully be successful.

I did find people talking about trying to put the database into read-only mode and then attempting a backup but I'm not sure how to do that.

If I do that and it too fails is there any way to export any of the data in the corrupt database so that it can either be manually re-entered or imported? I tried printing some posted invoices but it tells me that there is no space available in the database and to expand it and try again but that just results in the same 100% used problem. I saw some mention somewhere of this being an issue of having an invalid pointer at the end of the database file.

I can copy invoice lines and past them into excel. I see that works okay but that would take forever as well.

But over two weeks of work is insane.

I also thankfully have an intermediate mySQL database that my UPS and FedEx machines use to read invoice data from as my Sales Invoice form contains code that writes to it via ODBC to that database automatically.

So what should I try to do here? I saw many mentions of C/DART blah blah but how long would that take and what ungodly amount of money would they charge. And even if I did that what about the data we're currently putting into Navision how would we merge that?

Anybody know the best way to handle this? I appreciate all help. Thank you.

David_Singleton

emulsified wrote:

I can open the crashed database from 3/29/2012, it is about 21gb in size. It opens and says "recovering list of free blocks" then I get an error message stating there are errors in the database. When I look at the free space it shows about 30% free space in the database.

When I close the database and open it again it shows 100% used. I tried expanding it and it just keeps consuming any amount of space I add to it.

Are you doing this on a copy or did you do this on the only copy you have? If so then in recovering free space you may have destroyed the database.

emulsified wrote:

I did find people talking about trying to put the database into read-only mode and then attempting a backup but I'm not sure how to do that.

Open a command prompt at the fin directory and use the dbreadonly parameter

C:\Program Files (x86)\Microsoft Dynamics NAV\Nav400Sp3>fin dbreadonly

Now Nav can not force the recovery of free blocks so it will not go into self destruct mode.

emulsified wrote:

I saw many mentions of C/DART blah blah but how long would that take and what ungodly amount of money would they charge. And even if I did that what about the data we're currently putting into Navision how would we merge that?

C/DART is a great tool, its only problem is it is not available to partners, which makes it basically totally useless. The idea of sending the database to Microsoft for recovery is crazy. The work can be done remotely over Terminal Server. IF you could get the tool.

C/DART is only useful if you can recover the database the same day (or through the night) before starting business the next day.

Since you have a lot of data added since the crash, the merge of that data would be a huge job.

emulsified wrote:

Anybody know the best way to handle this? I appreciate all help. Thank you.

Generally databases can be manually recovered (even if you have lost the free space). It looks like the corrupt disk section is part of the primary key of the GL Entry table. The good news is that this is an easy table to reconstruct and can generally be reconstructed correctly (unlike the Item ledger which is difficult). The bad new is that if the corruption is recently new it could take a lot of painstaking work to reconstruct.

The hardest part of the whole process is to delete the corrupt records whilst keeping the good ones.

David_Singleton

emulsified wrote:

When I attempt to do a backup it starts then stops around G/L Entry with the error message that there are errors in the database and promptly stops.

I forgot to say that Navision stops at the first error, so it might be just one GL Entry crashed or (since that is table 17 of hundreds) it might just be the first of many errors, so step one is to find out how many damaged tables there are.

emulsified

David,

First and foremost thank you for the information it was very helpful. I have multiple copies of the database file made, that was the first thing I did so I would be able to do multiple operations.

When I put the Navision client into read-only mode fin.exe dbreadonly it still did a check for free blocks but did not error out this time.

The backup got to almost 70% of 2,097,### of file 1 and then failed with the corrupt message. I wasn't watching the whole time to see at what table it was when it failed.

I was able to print all of my posted invoices from 03/09/2012 to 03/29/2012 to a PDF file which is great because they can be re-entered. I've already prepped the successfully restored database from 03/09/2012 by creating new SIO, SI, CN, PO, PI lines with number sequences that will be beyond the range of the last numbers used on 03/29/2012 so there won't be any weird duplication of document numbers that we actually care about if we definitely have to manually re-enter data. I know I can change the option to allow manually choosing of an SIO# but not the Posted Invoice#.

Is there any way I can export some of this data to be imported into my 03/09/2012 successfully restored database such as Customer, Sales Invoice Orders, Posted Invoices, Accounts Receivable (posted payments), Items (new or changed)? What report can I run to give me a list of all the payments posted to customer invoice/accounts as I will need a list of those handy as well?

I've done so much in dealing with Navision for the past almost six years.
If any of these can be done easily would you be so kind as to give me a couple of links that might guide me through it or you opinion on the current state of my issue.

Thanks.

Savatage

know I can change the option to allow manually choosing of an SIO# but not the Posted Invoice#

There are fields on the sales header called "posting no" and " shipping no".
If they aren't on a tab you can add them. You can manually enter the invoice and shipping numbers so they match what the customer already has. Also if you can print them why not export using a data port the posted invoices so you can import them as sales orders for reposting? And I'm guessing you already found out that keeping you backups on the same server isn't a good idea. We backup to a different server raid1 used. And that daily auto copies using windows scheduler the files to a external hd - just to have another option. A 1tb ext hd is real cheap: http://mobile.buy.com/ibuy/Product.aspx?sku=212218324
Well worth the cost of re-entering weeks worth of data.

emulsified

Savatage,

Thanks. Appreciate the advice. The database will only open without errors in read-only mode. If I attempt to open it without read-only mode it tells me there is an error when it tries to recover free blocks and then the used space jumps to 100%.

You mentioned using a dataport to import the posted invoices as sales invoice orders (SIO). If the database is in read-only mode I'm guessing I can still use an existing dataport to do so, if so which one? I don't think I can create a new one as the database is in read-only mode am I correct?

Thanks.

Savatage

Do you have n/odbc installed? Can you export data to excel with it?
Or copy selected lines from the nav tables and paste them into excel?
Getting the data out is one step.

emulsified

I don't have N/ODBC installed.
I can click run on the tables to display their data.
I can copy and paste data from NAV tables to Excel.

On another note: When I started Navision from the command line with fin dbreadonly but I don't think it actually opened in read-only mode, but I didn't get any error when it was recovering free blocks. The backup did fail but much further through. So I closed it and opened it again the command line using fin database=N:\fin.fdb, dbreadonly=yes and noticed the title bar showed "read only" whereas I did not see that before. I then thought if I didn't see that before it probably wasn't in read-only mode and Navision probably wrote to the database file so I thought I had better start over with another copy, when I exited this time Navision gave an error message about not being able to write to the database because it was in read-only mode whereas I didn't get that message ever before so it must have not been in read-only mode.

I'm trying again on another original copy of the db file this time I used the command line fin database=N:\fin.fdb, dbreadonly=yes and this time I noticed Navision started up and the end of the title bar shows "read-only" and I didn't see that last time using just "fin dbreadonly".

So needless to say I'm going to try a backup again with a fresh copy of the database file to see what happens. I don't have much faith in it though. But it never hurts to try.

But in the meantime how can I proceed with copy and paste?

Savatage

I don't know what you mean about proceeding with copy and paste.
You can start with posted invoices. Can you go to designer and the table directly?
Filter the posting date for one day to test. Copy the results and paste them into excel.
Before pasting into excel select the excel table and make all cells TEXT and then "paste special" -> text. Just to be sure your not losing any leading zeros. Then you'll have to go to the posted sales line table to get the corresponding lines.
From that data you can create a csv or tab file that you can import. You'll probably have to create you own data port.
I know there are a bunch of posts about importing sales orders on mibiso. I can't go into more detail now because typing on an iPhone is killing me right now.
Hopefully the backup works!

viewtopic.php?t=16024
Scroll down this topic towards then end if you need help regarding a simple data port.

emulsified

The backup didn't work.

I was able to copy all the records in the Posting Date range from the Sales Invoice Header and Sales Invoice Line tables into Excel as you explained how to do.

No that I've got this data it needs to go into the Sales Header and Sales Line tables respectively so they can be re-posted.

I have about 1,300 invoices of data and I'm trying to save 4 people from about 5 days of non-stop invoicing. If I can import these in as Sales Invoice Orders into the Sales Header and Sales Line tables with their pre-assigned no. (posting#) then I should be able to just post each Sales Invoice Order as normal, possibly even a batch post?

Again, thank you so much for all your help. Not sure if this is your full-time profession, but you seem to have or know where to get all the answers fast. Thanks.

Savatage

It's far from my profession. My biz has been using nav for a long time. And I like to read up on everything that's going on do I can continue to learn. I like to know what nav can to and push its limits. It's amazing how much it can do past what you get out of the box. Also if something like what happened to you occurs I won't be freaking out.

emulsified

Thanks. I appreciate your calm and cool thoughts on my issue. I guess I'm freaking out because (downtime) this past Friday everyone was just standing around doing other things because of not being able to do any invoicing or order entry.

It looks like for now I'll have everyone start the following:

1. Enter any new Items or Item changes made in the missing date range from data I got out of my corrupt database tables (3/9/12 list compared to 3/29/12 list).

2. Enter any new Purchase Orders in the missing date range from data I got out of my corrupt database tables.

3. Enter any received item quantities on any Purchase Orders, I copied this from the corrupt database from the Purchase Lines table filtered by Posting Date.

4. Enter all the Posted Invoices from 3/9/12 to 3/29/12 as Sales Invoice Orders again (hoping to be able to use the same SIO# and Posted Invoice#'s, SIO#'s no problem, Posted Invoice#'s I think you said add the box to the form for Pre-Assigned No). In the meantime while they are doing that maybe I can come up with a nice dataport to bring in whatever is left or at the least some ADO grabbing the data I copied out to Excel and then in turn to a temporary mySQL database from which I can then populate the Sales Invoice Order form like I already do when I import FedEx and UPS tracking, etc. from our intermediate mySQL databases.

5. Enter any payments received from 3/9/12 to 3/29/12 (we have a paper ledger backup).

I think I have the correct order of things in accordance to their dependencies for our concerns and Navisions concerns.

I believe doing the above will bring us back up to speed within a weeks time. If you have any suggestions that might help I do appreciate them greatly. Since you are in L.I. maybe someday when I'm in the city I can attend your "Don't freak out it's just Navision been there done that courses." J.K.

Thank you again.

Savatage

We had a server crash a few weeks ago so I know how you feel when everyone is standing around asking is it fixed yet.
Fortunately I purchased an old used server for a few hundred bucks. And we back up daily overnight. So I was able to restore in the backup server so people were able to do some basic stuff and not have the company come to a halt ( my worst nightmare too). So we used that while I fixed the main server. A sever crash is not something as a biz owner you usually play with or test Alot. The best you can do is have a plan of action and hope it works as expected when you need it to. I was even carrying a pocket flash drive around with me that contained a copy of the product cd as well as a zipped copy of our backup files so I would be able to reinstall and restore from anywhere. Once I got my FTP working I have the backup auto zipping and sending it to the web using scheduler and winzip. Sounds like over kill having the backup in three places but the FTP is for fire reasons.

emulsified

Thanks for all of your help. I had the scheduler set for every night to stop the database SERVICE (everyone always logs out and shuts their machines now), make a copy of the entire FDB file to another set of mirrored drives (not striped) and also an actual backup from within Navision (using naviback lite) and that was stored on the mirrored drives as well as a USB stick when someone remembered to plug it in (found out nobody plugged that in for a few months). Also was using sugarsync to get the 7zip files of the nighly backups offsite which ended up being corrupted anyway.

We had a combination of problems with our ISP provider Comcast for the past 2+ weeks. Something went haywire and I think somehow sugarsync got messed up. But anyway the hardware was on its way out I guess there was nothing I could do about the fact that the DB must have had some corruption and the resulting backups also did which I can't understand unless it was the hardware/bad data/corruption during backups also within the zipped backups making them useless after the 3/11/12 backup. I only verify the backups by doing a restore with Navision once at the beginning of each month because of the time it usually takes. Maybe I should be doing that weekly. Our current setup was Server 2003 x86 with HARDWARE RAID 1 which got totally messed up, mainboard issue, etc.. The server was completely dead and wouldn't even post. I ended up using Runtime Software's RAID utilities to get what files and database copies I could off of the mirrored set.

Using my backup server right now. It's not as powerful but it will get us by until I order the new server tomorrow. Is there anything that can be done in terms of verifying the Navision backups? I haven't had really any trouble like this in the almost 6 years that we've been on Navision now. Can I look forward to these issues happening again? Do you have any recommendations in terms of hardware or backup procedures you are using that I should consider using?

Really appreciate it. Thanks.

Savatage

The only other thing we do is every morning I delete the company from the backup server and restore last nights backup. That way we are only one day behind at the worst

emulsified

Sounds good. Has it been your experience that if a Navision backup succeeds then generally a restore will be successful provided that the backup file doesn't become corrupted on the disk?

Thanks, I really appreciate it.