| Author |
 Topic  |
|
|
AnonJr
Moderator
United States
5768 Posts |
 Posted - 07 December 2005 : 16:12:14
|
One of the guys in the office comes up to me with a sheepish look and says "I think I made a big mistake with the CPR Database..."
-- Not good. --
I go and check it out and find that he thought he'd made a shortcut to the Access database on the department's shared drive. Instead he made a copy of the database on his computer. This happened back in Sept. He didn't notice it until a couple of days ago ... that's right: after he'd been updating his copy and others had been updating the other copy. 
I now need to try and reconcile the differences between the two. Anybody know of a relatively painless way of doing this? I'm trying out an app that claims to be able to do it but its been fairly incomprehensible as to what its doing... not to mention the less-than-helpful help. 
Any and all thoughts are appreciated. |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 08 December 2005 : 04:23:39
|
I find a big stick normally helps, though not with the db 
I have seen a couple of db comparing tools around, but never actually had the need to use one. |
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 08 December 2005 : 06:05:51
|
Hmmm - don't suppose he had replication switched on and can just merge back? Didn't think so :(
It is going to be a manual job. I would suggest exporting each of the tables from both db copies to a text-format file of some kind, sorted in the same order (not necessarily ID order, but maybe Surname, have a think and experiment a bit, see which is most helpful in this case).
Now go and download a copy (even a free trial) of 'Beyond Compare'. It is the daddy of comparison tools - compare your files and it will help you identify differences.
Now, decide on the master data set (ie the central one, most likely), BACK IT UP, and slowly plan how to migrate the new records in - start with parent tables, then work down to their children etc...NOTE ANY AUTONUMBERS WILL HAVE CHANGED SO YOU WILL HAVE TO MAINTAIN THE KEYS MANUALLY!!!! Was that loud enough? This is really important, as this is your chance to go from 2 semi-useful datasets to one completely ruined one!
It's a manual job - depending on how confident you are with the SQL language aetc , you may be able to make yourself a load of shortcuts, just make sure you do it in a very controlled manner, be very methodical, and test the backside off it when you're done - get everyone to pick a dozen or so of their own records, and compare them before and after the merge.
Oh, and good luck! It's not hard to sorth this out, just a bit manually intensive if you want to have total confidence!
|
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 08 December 2005 : 12:42:44
|
yeah I was afraid of all of this. I've been working with some copies of both of the databases in question (you didn't really think I'd work on the originals did you?  ). I tried a program aptly called 'Compare Database' which has been great in generating difference reports. Unfortunately when I try to use the copy feature to merge the two together it crashes  (read 14 times in 2hrs). I'll give Beyond Compare a try...
If I can't get it to work by today I'll tell him on Friday that he needs to plan on re-doing a bunch of data entry. Fortunately for him its the slow time of year for his classes.
Oh, and don't think that a few whacks with a baseball bat didn't come to mind!  |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 08 December 2005 : 12:45:05
|
quote:
Originally posted by AnonJr
If I can't get it to work by today I'll tell him on Friday that he needs to plan on re-doing a bunch of data entry.
Sounds like the best option all 'round; his mess, let him clean it up!
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 08 December 2005 : 12:55:56
|
| I think a bit of strategic re-keying will help him appreciate just *why* they hire techie people, and it'll help him appreciate your value...share the pain ;-) |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 08 December 2005 : 14:54:17
|
Don't forget to "fix" the process that allowed this problem in the first place. :)
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 08 December 2005 : 15:09:34
|
The long and short is I just got it fixed. Fortunately he'd only added one instructor and a bunch of classes (there are separate tables for instructors and classes). Unfortunately my predecessor (who built the database) didn't enforce referential integrity.  That really didn't help things.
With the help of the reports generated from the first program I tried, a few strategic update queries, and a whole heck of a lot of careful copy and paste, we now have one relatively good database.
... And I made a shortcut on his desktop and deleted the copy.  I hope the endless harassment will help instill a desire to double-check stuff like that in the future ....
I found out that 'Beyond Compare' doesn't natively support what I was trying to do, and someone had written a plug-in that did. The plug-in was about as useful as the first program that I'd tried. I will admit though, it was much more user friendly about it.
Thanks all for the help. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 09 December 2005 : 04:21:24
|
Good feedback :)
Just for the record, I personally prefer to expoet everything to text files in a case like this, just makes it easier to sanity-check, in which case Beyond Compare is beyond compare |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 09 December 2005 : 08:57:45
|
| This was one of those times that I really wished they would let me put together a multi-monitor setup. Working with two tables open side by side in a 17" monitor isn't exactly my idea of fun. |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 09 December 2005 : 09:20:07
|
Fair play to you, getting it sorted so quick, though
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
| |
Topic |
|
Topic Locked
