Data Corruption and backups

I went to a new customer yesterday with 5 years unarchived and now corrupt Sage 50 data, 143000 transactions and both his daily backups also corrupt. The Fix option in Check Data doesn't cure it and he is going through the options now - all disruptive and costly. So the lessons for us all are:

1. Backing up corrupt data and overwriting a good backup is worse than not backing up at all - when you do a backup and Sage asks do you want to check the data, click YES - especially if you've had a power cut that day!

2. Archive your data at year end and clear the audit trail - too many live transactions doesn't just make everything annoyingly slow, it makes corruption more likely especially with hardware problems or power cuts as it takes longer to update all the tables.

3. Get a UPS for your server or the PC where your accounts data lives. 

That's my sermon for the day and to all Sage 50 users, may your god protect your data (but try to look after it yourself too, just in case)

Tony

Comments
Witch-Queen's picture

AMEN!

Witch-Queen | | Permalink

1) I would recommend a Full backup once a week (say on a Friday), and a Data Only backup every other day. Keep the Friday backups for at least 3 months, preferably off site. If you are using an older version of the program, remember to change the name of the backup file to today's date every time.

2) Clearing the audit trail to year end is a novice mistake. Do you really want to loose all your Customers and Suppliers history?  The clear audit trail option askes you to enter a date to clear up to, and I have seen many use the year end date only to then find that they have to restore the year end backup just to look at Customer or Supplier History when there has been a querie . You should choose a date at least 6 months, and preferably a year, BEFORE year end.

3) Totally agree.

'May your god protect your data (but try to look after it yourself too, just in case)' should be nailed to the wall in every office, regardless of what software they use.

I hate to say this but, sending the data to Sage for repair would probably be your clients cheapest option. They offer the service for a fixed fee, regardless of how badly corrupted the data is.

-- Witch-Queen

Data Corruption

johndon68 | | Permalink

"I hate to say this but, sending the data to Sage for repair would probably be your clients cheapest option. They offer the service for a fixed fee, regardless of how badly corrupted the data is."

There are Developers/Business Partners who will fix data for a LOT less than Sage would charge and, unless you pay the extra for their overnight service, quicker as well :)

John

completebookkeeping's picture

back up repair

completebookkeeping | | Permalink

-- Complete Book-Keeping Ltd

If your stuck on the repair, I would be able to get a quote for you.  Let me know - info@completebookkeeping.co.uk

Sage Accredited Accountant Partner.

 

ps.  I also encourage, when templates, reports have been updated, do a back up of just this section and no data - just in case computer goes down completely you can install all your saved templates.

Fixing Data Corrupt Sage

jeh | | Permalink

We have data corrupt and now just 30 items listed that we cannot amend or 'fix'

 

Could you put me in touch with any independent Sage technicians please?

jehfarm@aol.com

 

 

-- jh

Data Corruption...

johndon68 | | Permalink

I offer a 'no fix, no fee' data fixing service and I'd be more than happy to take a look at your data for you.

John (Certified Sage Developer) 

Bob Bishop's picture

sage data corruption

Bob Bishop | | Permalink

Recently I discoverd data corruption which was not picked up on the daily data check routines. Won't bore you with details but basically the list of suppliers  differred from the nominal ledger account ( taken to 31/12/2099 cut off to elimante the usual incorrect dating errors).  Although sage was able to fix this problem free of charge, as disc had to be posted to Newcastle for repair, meant that we could not operate sage for three working days, so was some administration bother and cost.

 

My concern is why such discrepancy was not reported on the data checks errors list.  Has anyone else experienced this?  I have never experienced this before in some 20 years of sage accounting.

Anne Marie Sewell's picture

Sage data corruption

Anne Marie Sewell | | Permalink

 Hi, to confirm, I've also used Sage for just over 20 years but many times have encountered data corruption problems that are not picked up by the Maintenance function. Have even seen an unbalanced Trial Balance when the data check is clear. 

The advice to keep the number of entries down is sound.

Archiving, clearing down old data and and then compressing the data can often fix corruption.

If only clients would listen though regarding data checks and care with backups!

kevin503's picture

Data fix

kevin503 | | Permalink

I can certainly recommend John having recently used his services for client data. Corruption corrected overnight. Professional and effective.

I had the situation recently where I "fixed" minor data corruption (v16) via the usual re-index-compact then fix option. Data check reported clear - but very quickly became corrupt again. The lesson I learned was that no errors, no warnings, should not be taken as proof the data is properly fixed.

I wonder if there may be more data corruption going on in larger data sets than the Sage check facility would have us believe?

Data Corruption

johndon68 | | Permalink

Thanks for that Kevin.

I see a lot of sets of Sage data and one problem that does appear to be coming increasingly common is duplication of transaction numbers.  The transaction index files gets corrupt and, as a result, new transactions are given the numbers of transactions that already exist - the big issue with this is that, whilst this does cause initial errors, in many cases, the Fix option appears to fix the errors (which was the case with the data Kevin had) but the duplication remains and gets worse thereafter.

Whilst there is no way, within the program, to correct this (the data has to be sent away to be fixed, either to Sage or, if a quicker fix is required, another company offering data fixing services), it is possible to check if you do have this sort of corruption as follows:

The immediate way of telling is to go into Financials and see if there are any duplicated transaction numbers.  Alternatively, run a ReIndex and the go the ACCDATA folder containing the data in question and look for a file SPLITIDX.DTA - if the file is missing or has a zero length then the ReIndex has been unable to reindex the transactions due to a problem with the transaction numbers.

John

 

Bob Bishop's picture

sage data corruption

Bob Bishop | | Permalink

Many thanks for these replies.  I must admit that accounting for a smallish company, around 60 employees, when confronted with technical accounting snags regarding sage one is a bit isolated. I really could not believe what my eyes showed me, creditors did not equal the "control account" (in old manual bookkeeping parlance) but the error was very clear.  Had in past such errors but always from sage data check error report. Once this corruption came about through flash of lighing during back up, for instance.  These events I could accept as "act of god".

So I am at least assured of my sanity from knowing that others in the outer world also experience such problems. No matter how helpful Newcastle may be in fixing problems that arise they are, naturally, not forthcoming on pointing out design flaws.  Well I suppose no suprises there judging from the numerous other complaints over sage.

 

But now left with considering what to do, although our data goes back several years the nature of our business, in contracting, does compell me to retain history for at least two to three years.  Have cleared audit trail a few times (archiving before this) so that can stretch back if needed this is a bit cumbersome.  But if needs must I will do. 

 

One other comment is that never had this type of problem on earlier versions of sage.  Currently on Sage Professional 2009, version 15. 

Anne Marie Sewell's picture

Data Corruption

Anne Marie Sewell | | Permalink

I don't think it's the version. I've seen it on Instant, from eons ago and aso on much earlier versions than 15. Yes, I agree, fiddly, but archiving and keeping the current data as low as possible is the best solution.

And I know about the feeling of being mad. When I phoned support to say the trial balance didn't balance, I was told I needed to do a training course. Ho!!! Fraid I wasn't too polite about that! No, they will not initially agree to the problem, but persevere, they do in the end.

 

 

 

Versions

johndon68 | | Permalink

I've seen data corruption on all versions and I've not fixed data on v2009 more than any other versions.  

The basic problem is the 3 tier structure to the transaction files in Sage - if something goes wrong after one tier has been updated but neither of the others have then you get corruption and, for the most part, this is what I see all the time where, for a given transaction, data in one tier does not match the data in the other tier(s).  

Until such time as Sage change the underlying database such that, if there is an update problem the changes can be rolled back, this will continue to be an issue...

John

Witch-Queen's picture

TB Not balancing in Sage - it's a known issue

Witch-Queen | | Permalink

A Trial Balance that does not balance in Sage may be caused by an incomplete chart of accounts.

Go to Chart of Accounts - highlight Default - click Edit

Click the Check button - if it asks you to print or preview then there is a problem with the mapping - click print to see what's missing or duplicated in the chart and amend it accordingly.

Your TB should now balance

BTW if this is the cause of the unbalanced TB then this will NOT show in Check Data

-- Witch-Queen

TB and the Chart of Accounts

johndon68 | | Permalink

Whilst a missing nominal code would stop the Balance Sheet from balancing, it does not affect the Trial Balance at all as the TB does not look at the Chart of Accounts - you can have no nominal codes at all in the COA and the Trial Balance will still balance...

One common reason for the TB not balancing is 'bucket corruption' where the monthly buckets on the nominal records become corrupted and, as this is where the TB gets it's data from the TB can become unbalanced.  Sage have recognised this enough to include an option (File -> Maintenance -> Recalculate Monthly Totals) that rebuilds the bucket values based on the transactions on the Audit Trial.  This option is definately there in v2011 & v2010 but I'm not sure if it goes back as far as v2009.

Even without this option, depending on the exact version of Sage 50, you can force a rebuild of the buckets by using the Change Financial Year option by changing the year to something else then changing it back again - if you ever have an unbalanced Trial Balance but no errors, this is always worth a try.

John

Obvious questions .... these guys are joke

JC | | Permalink

@Witch-Queen

It may seem obvious questions but how long has everyone known about these problems (presumably years) and why has nothing been done about them by Sage?

  • Data corruptions
  • TB Not balancing in Sage - it's a known issue

@johndon68 statement '.. The basic problem is the 3 tier structure to the transaction files in Sage - if something goes wrong after one tier has been updated but neither of the others have then you get corruption ..' simply goes to demonstrate the real problem with Sage products

So basically Sage have not implemented the transaction/rollback process whereby an error in any 'tier' of the transaction abandons the entire transaction and returns everything (rolls back) to their state prior to the transaction.

Even if the database at the time did not support this functionality it should have been coded which is not excessively difficult, but would have provided a more robust application

There are no excuses for this style of 'shoddy' development and quite frankly this renders any such Sage application 'not fit for purpose' - known errors that Sage has not addressed for successive releases

It also demonstrates the contempt with which Sage regard their customers in expecting them to accommodate known errors for many years whilst they continue to charge handsomely for program updates and data fixes - basically in their eyes all customers must be suckers to put up with such a policy

The aspect that is so extraordinary with Sage is that everyone jumps to their defence over what are essentially indefensible applications

As for including maintenance functionality (File -> Maintenance -> Recalculate Monthly Totals) - this a complete joke and a clear admission that they have woefully failed to do the job properly in the first place

Frankly it is not overly challenging to write an accounts package but this whole thing just goes to show that marketing trumps ability in every instance - well done Sage keep up the marketing!
 

Corruption

johndon68 | | Permalink

@JC - "As for including maintenance functionality (File -> Maintenance -> Recalculate Monthly Totals) - this a complete joke and a clear admission that they have woefully failed to do the job properly in the first place"

Not sure I'd entirely agree with that - the corruption of the buckets can happen for reasons completely out of Sage's control (power failure, network problems etc).  By adding this option, they have provided the user with a mechanism to recover from this issue without the need to restore a backup or send the data away to get fixed - surely a good thing as far as the user is concerned?

John

 

No commit if transaction not completed ....

JC | | Permalink

@johndon68 - agreed that things maybe outside Sage control (power cut)

BUT the whole point of a Transaction is that if there is no 'commit' then nothing is changed - so a power cut would prevent the 'commit' being executed and therefore the transaction would be abandoned

ergo ... no need for rebuild because nothing has been written

Data corruption and data size

tbayliss | | Permalink

Firstly a me too for JohnDon68 on data fixes. Did a great job for me. Sounds like this forum is keeping him very busy!

My experience is that corruption is far more likely the bigger the database. Because the updates to the data files take longer, it's a bigger time frame for a hardware or network blip to happen.

The biggest database I've seen was 500,000 but that was stand alone. I certainly wouldn't recommend this for standalone or anything over 200,000 on a network. So if you can't get below this even after archiving at year end and clearing as much of the audit trail as you feel you can on your live system, then it's time to look at something better and more robust, probably SQL based (and more expensive).

I was looking at a client with Pegasus Senior, which is surprisingly still on FoxPro, although Microsoft discontinued it quite a few years ago. A bit of a fiddle to get an ODBC link working on Windows 7/Office 2010 and a bit slow too.

Apart from the well known ones which are worth looking at - Sage 200, Access Accounting, etc - I offer for your consideration MyN (pronounced Mine) from www.accountingoffice.co.uk. I've done consultancy for 4 users including 3 new installations now and it's better than Sage 200 in my opinion - very powerful and flexible.

Tony Bayliss

Corruption in Sage

ghewitt | | Permalink

I agree with what has been said by Witch Queen; the problems with Sage have been well documented. There is no incentive to make it work by changing from an archaic flat-file system that is forced to try and be relational to an SQL database that is. We had a major corruption- don't ask how, I just sorted it. Data failed to write to the audit trail; data disappeared and accounts were wrong - the outstanding did not match the actual. It took me months to sort it afer Sage 'fixed' the data and I am sure we have lost considerable money, not to mention accountants fees in helping to unscramble the system.

We are stuck with Sage at the moment as other systems link into it. So what to do to prevent this happening again.

1. Before ANY major work - i.e. importing, batch inputting etc., check data and back up. I use the transaction number as the prefix; someone else's idea but a good one so you can at least print off the corrupted data trail from the last know good backup before you restore; then (assuming the restore worked ok) you have something to re-input your data from.

2. Back up the file server data twice a day. This need not be an oneorus task. Looking around I found that many of the backup-programs that do what I want cost in the hundreds of pounds. It seems if you put the word 'server' as part of the criterion the price jumps exponentially and many of the cheaper desktop backups don't schedule in a way I want, nor backup in the way I want nor run on Windows 2003 server. However, after much searching and reading of reviews I found one such program that runs on my Windows 2003 standard server. Macrium Backup - you can google it. It is CHEAP!!! Less than £50. Before anyone says anything, No! I am not a sales person for the firm nor have any links whatsoever. This is purely for your information. It just works and as a bonus it will produce a complete image of the partitions, using shadow copy, I understand so it won't interupt work, so if the hard-drive goes west you can do a bare metal restore and be up and running again in minutes (if you have a spare drive - yes I have :) ). Don't expect all the bells and whistles at that price but it is pretty comphrehensive. Set and forget. I have two data file backups a day and one complete image backup that goes to an off-site 2Tb networked hard drive used exclusivly for that purpose (£145.00 PC World). Incidentally, you can 'mount' the image as if it were a plug in drive and view the data as though it were a networked drive.

Why two a day? Well, one is at 02.00am ready for work at 09.00. The second is at 13.00. I do an end of day Sage backup at 5.00pm. his means that there is only 4 hours of work to reinput at any one time should the worst happen. We have a lot of entries; up to 50,000 a year.

Now you might think that that is OTT but, we have had two corruptions since and I have fixed them in no time. Peace of mind. What price that at less than £200?

 

 

Corruption

johndon68 | | Permalink

@tbayliss "Firstly a me too for JohnDon68 on data fixes. Did a great job for me. Sounds like this forum is keeping him very busy!"

Thanks Tony, much appreciated.

John

Witch-Queen's picture

Re: TB and Chart of Accounts

Witch-Queen | | Permalink

@johndon68

'Whilst a missing nominal code would stop the Balance Sheet from balancing, it does not affect the Trial Balance at all as the TB does not look at the Chart of Accounts - you can have no nominal codes at all in the COA and the Trial Balance will still balance...'

In the real world yes, but this is Sage remember!

Like I said in the title of my post: this is a Known Issue for Sage 2009. If a user has created a new nominal (and ignored the pop up telling them that the new code is outside the chart of accounts, especially where the new code is on the balance sheet), then this can throw out the TB.

This was fixed in V2010

I realise this is slightly off the topic of data corruption, but I threw it in as others had mentioned the TB being out of balance in this and other threads. I found out the answer recently when another member of this forum contacted me directly with exactly this problem. Before running year end the TB was fine, but after running it the Brought forward TB was out of balance.

- Witch-Queen

TB & COA

johndon68 | | Permalink

@ Witch-Queen: "In the real world yes, but this is Sage remember!"

Indeed so :)

Just to confirm, the problem manifests itself only after a year end has been run and then only if the nominal code has been excluded from the COA?

John

Witch-Queen's picture

Re: TB & COA

Witch-Queen | | Permalink

@johndon68

Yes John exactly, sorry if I was not specific before

The TB was in balance pre year end and the nominal that was missing from the COA (a bank incidentally) was showing correctly.

However, after year end was run the B/F TB showed that the balance of this bank nominal had changed which put the TB out of balance.

The person concerned was running 2009 year end before then running 2010 when she spotted the problem. The incorrect balance of this bank remained during 2010 even though the balance was Nil by year end. However, by correcting the COA before running YE 2010 this corrected the problem and the TB is now correct for YE 2010.

-- Witch-Queen

T/B out after year end

19kings | | Permalink

We have also had this problem with several clients here in ANZ (Australia and New Zealand).

The issue is caused if there are multiple COA's. In this case the last COA in the list MUST be a complete COA even if it just shows ranges for each section.

The main COA that is used might be complete, then there may be two or three sub COA's for say just sales figures for the Sales Manager etc. If any of these is incomplete then one has to add a final COA which is complete and checks as No error found in this COA. This appears from our experience to solve the problem.

sage, bucket corruption

sue of the swan | | Permalink

Hi

I have read all through this page and have to say that its all very interesting, thank you.

I had a problem with the so called "bucket corruption" the first time I was working at a clients premises on their sage version, not sure which one it was but anyway, I did their year end and sent the backup to the accountant and he came back some time later and said your trial balance does not balance, this might be due to data corruption, I have worked with sage for 9 years now and never heard of such a thing, read up about such corruption and thought the only option was to contact sage for help, they advised that we needed to upgrade and then they would repair it, so my client paid up over £100.00 and got the problem fixed.....

Now some months later working on my own multi client 50 2008. one of the companies on my sage had the same problem when I tried to do a year end the trial balance would not balance and I thought oh no what to do, mines an old version and it would be very expensive to upgrade, I checked all my other clients/accounts on my sage and they were all fine because I did a year end and tb and all was fine except this one.......upon reading your words above I thought id just check all the different accounts and the only difference I could see between the one corrupted client and all the others is the addition of a bank account, which when I checked the COA showed up as not included, so I added the 1251 bank account to the COA and...it worked!, I did the year end and the trial balance balanced, ive also checked other nominals and they are all fine.

And now I think about it my client with their own sage that paid for an upgrade etc etc also has extra bank accounts that took them out of the COA.

my question would be to sage is do they know that this could cause a problem, and if so why make people upgrade and pay shed loads of money when it can be easily fixed?

Anyway its thanks to your help, all you above that ive sorted it out, and hopefully others will try this before contacting sage eh!

Sue

Add comment
Log in or register to post comments