Accidentally deleted customer account - how to restore? (HQ)

I was merging duplicate customers the other day, and deleting the unused accounts. This is a fairly regular occurrence around here, and using the MS procedure for merging duplicate accounts works pretty well.

After the accounts are merged, you have to use a WS601 to delete the unwanted accounts. Well, apparently I mistakenly added account #

000010000001 to one of my WS601s...

This was 6 days ago, and my bookkeeper generated statements today. Being account #1 makes you kind of memorable, so she noticed right away that the first statement wasn't for the right customer.

I can restore from backup to a new database, both store and HQ if necessary. Is there any way to restore a single customer record (with purchase history and A/R history)?

Tom

Reply to
Terrible Tom
Loading thread data ...

I'm sure you've alrady figured out that this is not really good..

I think that you are going to be lucky because you have backups. You should be able to reinsert the customer record and reconnect it to all of it's associated tables.

There is a command in SQL that will allow your to temporarily insert values into Identity columns (Customer.ID for example).

Run the following command: SET IDENTITY_INSERT Customer ON

insert the deleted record values from your backup

Then SET IDENTITY_INSERT Customer OFF

Do this at HQ First, then at each store. You would not want anything else writing to the tables at this time, so make sure you do it after hours - and on a test system first! I think this is going to link up all or the transaction and payment info, but you may have to do an A/R adjustment to get things back together... I haven't looked at this stuff for quite a while nad there may be some cascade deletes that I'm missing here - I know you'll get purchase and payment history back, just not sure it will restore your A/R correctly...

Reply to
Glenn Adams

Thanks for the tip, Glenn. I will try it this weekend and reply to this topic with my results.

Thanks again, Tom

Reply to
Terrible Tom

Glenn,

As far as I can tell, it worked. I was able to restore both HQ and Store Ops databases as new databases called CustomerX_HQ and ...X_SO, then use a SELECT *...WHERE Customer = 'X' query to recover the data. I exported a .csv file and used a combination of Word & Excel to delete the extraneous columns and build a pair of INSERT INTO statements to re-insert the pertinent data into the Customer table.

The SET IDENTITY_INSERT query didn't want to take the first time--I had to run it three times, I think, before my INSERT INTO query would run without an error.

After successfully inserting the restored data into my Customer table, all of the customers' purchase history and A/R was already there.

Also of note, we close our billing cycle on the 20th of the month. The customer was deleted on the 14th and had made a payment on the 7th. I was able to change the LastClosingDate from 1/20/2008 to 2/20/2008 and then use the reprint statement feature to print a 2/20 statement--and the statement was accurate!

Thanks again for the tip.

Tom

Reply to
Terrible Tom

Wow Tom - nice work. I was watching this post for your responses. I am so happy to hear that you were able to recover the customer and all data.

Jocelyn

Reply to
jocelynj

For future merge operations, you might be interested in our V.I.P. Zone add-in that allows you to tag and merge duplicate customers as often as required. This tool won't delete customer accounts in error and it is designed to work for both single stores and multi-store HQ environments.

A free 60 trial is available for your evaluation.

To view merge in action, watch the V.I.P. Zone Part 1 tutorial from our tutorials page and skip to just past half way. DRS add-in tutorials:

formatting link

Reply to
DRS Support

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.