OK, David, my mind doesn't want to get around your second sentence easily this morning...
The log would have to be of 9 tables, so 9 logs... All the tables are related by invoice_id. So, what you're saying is table by table -- cursor loop? -- check each log entry for changes between the backup and restored dbs based on the update timestamp? I'll definitely look into this as well as making more incremental backups during the day. Thanks David. Claudine > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M. > Blocker > Sent: Monday, March 21, 2005 7:59 AM > To: RBG7-L Mailing List > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 > tablecorruption > > Claudine > > Why not use Bernie's system of a log of changed / inserted rows in > combination with your table? > > If your table to be updated as a PK column TablePK, and the update record > table has ALL the columns from the main table, with copies of the data as > of > the date of change/insert PLUS the date/time of insert/update, you could > find the rows that have been inserted/changed after the date / time of the > database which you are restoring from, and insert the new rows and update > the changed ones from the backup table data. > > David > > > David Blocker > [EMAIL PROTECTED] > 781-784-1919 > Fax: 781-784-1860 > Cell: 339-206-0261 > ----- Original Message ----- > From: "Claudine Robbins" <[EMAIL PROTECTED]> > To: "RBG7-L Mailing List" <[email protected]> > Sent: Sunday, March 20, 2005 8:21 PM > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 table > corruption > > > > Alastair, > > > > I know Murphy's law (if something can go wrong, it will) but I've never > > heard of Sod's... Good point on the temp tables, I have 9 on my main > form > > for each record. > > > > Thanks for sharing :) > > > > > -----Original Message----- > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Alastair > > > Burr > > > Sent: Sunday, March 20, 2005 6:09 PM > > > To: RBG7-L Mailing List > > > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 > table > > > corruption > > > > > > Claudine, there's only one thing that you can be absolutely sure of > with > > > backups: overkill will never be enough! > > > > > > Murphy and Sod have their laws and you can't beat them - all you can > do > is > > > copy, backup, reload, unload - whatever suits you best - as often as > you > > > can > > > and then some. > > > > > > Whatever you do also be aware, if you use them, of temp tables being > lost > > > if > > > you can. I enter a lot of data via temp tables but don't append it to > the > > > main table until all is verified so I unload the temp data to a file > and > > > overwrite the file periodically. Of course, that point will be the > precise > > > time that the worst happens so paranoia is part of Messrs Murphy and > Sod's > > > legacy. > > > > > > Good luck, > > > Regards, > > > Alastair. > > > > > > > > > ----- Original Message ----- > > > From: "Claudine Robbins" <[EMAIL PROTECTED]> > > > To: "RBG7-L Mailing List" <[email protected]> > > > Sent: Sunday, March 20, 2005 9:17 PM > > > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 > table > > > corruption > > > > > > > > > > Bernie, > > > > > > > > In my transportation database, I write/append to an audit file each > time > > > a > > > > record is added or edited, but I'm only dealing with one table. I > also > > > have > > > > an incremental backup several times during the day from which I can > > > easily > > > > restore. In my invoices database, there are 9 related tables. > Writing > > > the > > > > data is simple enough, restoring it might be complex. > > > > > > > > I think I need to take a Backup 101 course. Backup and > documentation > > > always > > > > seem last on the to-do list... With the Oracle database -- now > > > successfully > > > > replaced with RBase :) -- we had a sophisticated daily backup to > tape > in > > > > place along the son/father/grandfather model. Not one single Oracle > dba > > > was > > > > ever willing to assure me that any of it could successfully be > > > restored... > > > > And to my knowledge, it was never even tested!!! > > > > > > > > Let's hear it from the list... What's everybody doing for backups? > > > > > > > > Claudine > > > > > > > > > -----Original Message----- > > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > Bernard > > > Lis > > > > > Sent: Sunday, March 20, 2005 2:03 PM > > > > > To: RBG7-L Mailing List > > > > > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 > > > table > > > > > corruption > > > > > > > > > > Hi Claudine, > > > > > For one of my customers, I have a tracking table. > > > > > Any time an item is added to the item table, or a sale is made, or > > > > > inventory > > > > > is replenished, a row is added to the tracking table which > indicates > > > any > > > > > of > > > > > the above actions and the date and time it ocurred, and WhoDidIt. > > > > > Though we haven't needed to use it for rebuilding purposes, the > store > > > > > manager uses it to determine why inventory is not what it is > supposed > > > to > > > > > be > > > > > and if any midnight requisitioning is happening. > > > > > > > > > > Bernie Lis > > > > > > > > > > ----- Original Message ----- > > > > > From: "Claudine Robbins" <[EMAIL PROTECTED]> > > > > > To: "RBG7-L Mailing List" <[email protected]> > > > > > Sent: Sunday, March 20, 2005 1:24 PM > > > > > Subject: [RBG7-L] - RE: Access Violation Messages and Sys_Layouts3 > > > table > > > > > corruption > > > > > > > > > > > > > > > > Ok gang, here is what I did to mitigate my problem. It's not a > > > complete > > > > > > solution but I believe the surgery was the least invasive for > the > > > > > patient. > > > > > > > > > > > > My backup from Thursday night when restored, didn't cause any > access > > > > > > violations :). However, it did trip RScope with that minor > > > sys_layouts3 > > > > > > problem which I fixed simply by deleting the two rows causing > the > > > > > problem > > > > > > and reloading the only row unloaded from the oldest backup which > > > didn't > > > > > > have > > > > > > that problem. As Ross Perot used to say: "Are you with me?" > > > > > > > > > > > > That left me with having to restore data entered on Friday which > I > > > > > > unloaded > > > > > > from the damaged db and reloaded into the corrected db. The > only > > > thing > > > > > I > > > > > > don't know how to do is to restore changes made on Friday to > > > existing > > > > > data > > > > > > which I guess will simply have to be re-entered. All in all, it > > > should > > > > > > work. > > > > > > > > > > > > Thank goodness for daily backups. But a question begs, how > would > > > you > > > > > > backup/restore changes made to existing data? Some kind of a > > > trigger? > > > > > I > > > > > > need an education on this subject now that I've had the hard > > > lesson... > > > > > > > > > > > > Claudine > > > > > > > > > > > >> -----Original Message----- > > > > > >> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > Sami > > > > > Aaron > > > > > >> Sent: Saturday, March 19, 2005 1:43 PM > > > > > >> To: RBG7-L Mailing List > > > > > >> Subject: [RBG7-L] - RE: Access Violation Messages and > Sys_Layouts3 > > > > > table > > > > > >> corruption > > > > > >> > > > > > >> Claudine - > > > > > >> > > > > > >> There are a couple more options: > > > > > >> > > > > > >> 1. Find a backup copy of the rb1 file from before the > corruption > > > > > >> occurred. > > > > > >> (NOTE: this will not work if you have made any structural > changes > > > to > > > > > the > > > > > >> database since the date/time on the rb1 file.) Copy the rb1 > file > > > into > > > > > >> your > > > > > >> working folder, replacing the existing rb1 file. Now, do a > reload > > > on > > > > > the > > > > > >> database and see if that helps. > > > > > >> > > > > > >> 2. Unload all for each of your database tables separately > like: > > > > > >> SET NULL -0- > > > > > >> OUTPUT IHopeThisWorks.str > > > > > >> UNLOAD str > > > > > >> OUTPUT SCREEN > > > > > >> OUTPUT IHopeThisWorks.dta > > > > > >> UNLOAD DATA > > > > > >> OUTPUT SCREEN > > > > > >> > > > > > >> Then, RBEDIT IHopeThisWorks.str and search for "CREATE VIEW". > > > > > >> Split this file into two files - everything ABOVE the first > CREATE > > > VIEW > > > > > >> statement into IHopeThisWorks1.str, and everything from the > first > > > > > CREATE > > > > > >> VIEW to the end of the file as IHopeThisWorks2.str. > > > > > >> > > > > > >> Then, RBEDIT IHopeThisWorks.dta and search for SYS_Layouts and > > > delete > > > > > the > > > > > >> whole LOAD section for that table. > > > > > >> > > > > > >> Then, get rid of the original database or rename it to > something > > > else. > > > > > >> > > > > > >> SET MESS ON > > > > > >> SET ERROR MESS ON > > > > > >> TRACE IHopeThisWorks1.str > > > > > >> --If no errors then > > > > > >> RUN IHopeThisWorks.dta > > > > > >> --If no errors then > > > > > >> TRACE IHopeThisWorks2.str > > > > > >> > > > > > >> If that still doesn't work, then I still have one more way up > my > > > sleeve > > > > > >> so > > > > > >> post another question here.... > > > > > >> > > > > > >> > > > > > >> Good luck! > > > > > >> Sami > > > > > >> > > > > > >> > > > > > >> > > > > > >> -----Original Message----- > > > > > >> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > > > Claudine > > > > > >> Robbins > > > > > >> Sent: Saturday, March 19, 2005 1:15 PM > > > > > >> To: RBG7-L Mailing List > > > > > >> Subject: [RBG7-L] - RE: Access Violation Messages and > Sys_Layouts3 > > > > > table > > > > > >> corruption > > > > > >> > > > > > >> Javier and Bernie, > > > > > >> > > > > > >> I have now reloaded the last 4 complete backup files created > with > > > > > unload > > > > > >> all > > > > > >> and every single one reports the 'data' error in RScope for the > > > > > >> sys_layouts3 > > > > > >> table. > > > > > >> > > > > > >> I also tried deleting both records in sys_layouts3 and RScope > > > reports > > > > > no > > > > > >> corruption. However, Access Violation messages continue and db > is > > > > > >> unusable. > > > > > >> > > > > > >> (how do you spell seperately?) separately. And that's a good > idea. > > > > > I'm > > > > > >> going to do that now... > > > > > >> > > > > > >> Claudine > > > > > >> > > > > > >> > > > > > >> > > > > > >> > -----Original Message----- > > > > > >> > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > > > Javier > > > > > >> > Valencia > > > > > >> > Sent: Saturday, March 19, 2005 1:03 PM > > > > > >> > To: RBG7-L Mailing List > > > > > >> > Subject: [RBG7-L] - RE: Access Violation Messages and > > > Sys_Layouts3 > > > > > >> > table > > > > > >> > corruption > > > > > >> > > > > > > >> > You could try doing a RELOAD or an UNLOAD of the structure > and > > > data > > > > > and > > > > > >> > then > > > > > >> > reconstructing the database. TTBOMK the SYS_LAYOUTS3 table > stores > > > > > only > > > > > >> the > > > > > >> > layouts of tables of views and it is not critical, if the > data > is > > > > > >> deleted > > > > > >> > it > > > > > >> > should just display the tables/views in the default format. > > > > > >> > Javier, > > > > > >> > > > > > > >> > Javier Valencia, PE > > > > > >> > President > > > > > >> > Valencia Technology Group, L.L.C. > > > > > >> > 14315 S. Twilight Ln, Suite #14 > > > > > >> > Olathe, Kansas 66062-4578 > > > > > >> > Office (913)829-0888 > > > > > >> > Fax (913)649-2904 > > > > > >> > Cell (913)915-3137 > > > > > >> > ================================================ > > > > > >> > Attention: > > > > > >> > The information contained in this message and or attachments > is > > > > > >> > intended > > > > > >> > only for the person or entity to which it is addressed and > may > > > > > contain > > > > > >> > confidential and/or privileged material. Any review, > > > retransmission, > > > > > >> > dissemination or other use of, or taking of any action in > > > reliance > > > > > >> > upon, > > > > > >> > this information by persons or entities other than the > intended > > > > > >> recipient > > > > > >> > is prohibited. If you received this in error, please contact > the > > > > > sender > > > > > >> > and > > > > > >> > delete the material from all system and destroy all copies. > > > > > >> > ====================================================== > > > > > >> > > > > > > >> > -----Original Message----- > > > > > >> > From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of > > > Claudine > > > > > >> > Robbins > > > > > >> > Sent: Saturday, March 19, 2005 12:36 PM > > > > > >> > To: RBG7-L Mailing List > > > > > >> > Subject: [RBG7-L] - RE: Access Violation Messages and > > > Sys_Layouts3 > > > > > >> > table > > > > > >> > corruption > > > > > >> > > > > > > >> > Hi Javier, > > > > > >> > > > > > > >> > I'm working with a copy. I've tried deleting both rows and > even > > > > > though > > > > > >> > RScope no longer reports the problem, the Access Violation > > > Messages > > > > > >> > continue... > > > > > >> > > > > > > >> > Claudine > > > > > >> > > > > > > >> > > -----Original Message----- > > > > > >> > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of > > > > > Javier > > > > > >> > > Valencia > > > > > >> > > Sent: Saturday, March 19, 2005 12:33 PM > > > > > >> > > To: RBG7-L Mailing List > > > > > >> > > Subject: [RBG7-L] - RE: Access Violation Messages and > > > Sys_Layouts3 > > > > > >> table > > > > > >> > > corruption > > > > > >> > > > > > > > >> > > Hi Claudine: > > > > > >> > > Whatever you do...backup first!!! > > > > > >> > > Then you can try deleting the offending items and see if > that > > > fixes > > > > > >> the > > > > > >> > > problem; if it does not, you still have the original... > > > > > >> > > Javier, > > > > > >> > > > > > > > >> > > Javier Valencia, PE > > > > > >> > > President > > > > > >> > > Valencia Technology Group, L.L.C. > > > > > >> > > 14315 S. Twilight Ln, Suite #14 > > > > > >> > > Olathe, Kansas 66062-4578 > > > > > >> > > Office (913)829-0888 > > > > > >> > > Fax (913)649-2904 > > > > > >> > > Cell (913)915-3137 > > > > > >> > > ================================================ > > > > > >> > > Attention: > > > > > >> > > The information contained in this message and or > attachments > is > > > > > >> intended > > > > > >> > > only for the person or entity to which it is addressed and > may > > > > > >> > > contain > > > > > >> > > confidential and/or privileged material. Any review, > > > > > retransmission, > > > > > >> > > dissemination or other use of, or taking of any action in > > > reliance > > > > > >> upon, > > > > > >> > > this information by persons or entities other than the > intended > > > > > >> > recipient > > > > > >> > > is prohibited. If you received this in error, please > contact > > > the > > > > > >> sender > > > > > >> > > and > > > > > >> > > delete the material from all system and destroy all copies. > > > > > >> > > ====================================================== > > > > > >> > > > > > > > >> > > -----Original Message----- > > > > > >> > > From: [email protected] [mailto:[EMAIL PROTECTED] Behalf > Of > > > > > Claudine > > > > > >> > > Robbins > > > > > >> > > Sent: Saturday, March 19, 2005 12:24 PM > > > > > >> > > To: RBG7-L Mailing List > > > > > >> > > Subject: [RBG7-L] - Access Violation Messages and > Sys_Layouts3 > > > > > table > > > > > >> > > corruption > > > > > >> > > > > > > > >> > > Hi all, > > > > > >> > > > > > > > >> > > We had a small surge at the office yesterday and one of my > db's > > > is > > > > > >> > > now > > > > > >> > > constantly giving off access violation messages which > cannot > be > > > > > >> > resolved. > > > > > >> > > > > > > > >> > > Upon running RScope 7.5, I find that there is an error in > my > > > > > >> > sys_layouts3 > > > > > >> > > table which cannot automatically be fixed because it > contains > a > > > > > note > > > > > >> > > field. > > > > > >> > > This table has two rows, one for an existing 'important' > table, > > > the > > > > > >> > other > > > > > >> > > for a view which I can easily delete and recreate. > > > > > >> > > > > > > > >> > > What should I do? > > > > > >> > > > > > > > >> > > Claudine > > > > > > > > > > > > > > > > > > > >
