Compare Databases to Check for Changes

[diggy8]

We built a departmental intranet website that can have all the content modified through the site by a series of web forms. We'd like to put in change tracking so that if something happens to the database, we'll know who's made changes since the last backup.

The best idea we've come up with is to pull everything from the live database and the backup and output any differences. It sounds great, but how's that done? In short, what's the best way to implement finding differences between two databases?

Thanks in advance!

 

[Voldo]

Hi, What you could do is add another column (or two) which writes old values to the database eg for a phone number column you could add a further column called old_phone_number and a date/time field, when a user changes their phone number then it writes the old value into the oldphonenumber column and also writes a date/time stamp into the other column, hope this helps.

 

[diggy8]

This would more than double the size of the database and double the processing time for any update queries. Still, it's another idea to consider, thank you!

 

[r937]

if something happens to the database, why would you not just recover it? (he asked naively, not being a database administrator)

is there no database log? might this be something like ms access?

i don't think you're looking for a coldfusion solution, and you'll probably get better answers by asking in the appropriate database forum

rudy

http://rudy.ca/

 

[Guest_imported]

You could send an e-mail to the database administrator everytime a form is submitted. Then he would be aware of and could track all changes

Cody

 

[diggy8]

Thanks for the feedback! I think I'll take a combination of ideas and have <cffile> create a log file that'll track each update. In the database corruption recovery plan we've laid out, restoring a backup is a last resort because the backups aren't automatic (three are created with a click of a button, but someone still has to click that button), there may be a lot of data lose when restoring the backup.

Now I'll need a way to generalize the <cffile> so that I won't have to customize it for each query (40+, ouch!).

Thanks again!

 

[Sarky78]

what database are you using for this? if you are using SQL server you can create a backup schedule that you can run when ever you want and that is automatic. You can set it to run ever minute if you want to !

 

[diggy8]

Sorry, should have said earlier. The database is in Access 97 now, with the possibility of moving to SQL Server later. It's quite a complex process to get something moved to SQL Server here, so unless we have troubles with it being in Access, it'll stay there.

 

[Sarky78]

could you not just use CFFILE to make a physical copy of the database. and use the scheduler in cf admin to execute this template ever hour or whatever, making a physical backup copy of your database.

possibility?

 

[diggy8]

We're using <cffile> to create a series of three backups right now. The original problem was comparing a backup with the current, but through the feedback I've gotten here, it looks like comparing two databases isn't too useful. If the current database is corrupt, you can't access it to find any changes anyway.

<cfschedule>, wonderful idea! One of the purposes of this site is to showcase what Cold Fusion can do. So if we can keep the auto back ups in CF, that's even better.

I have some code that outputs all the fields from a preceeding form, so if I combine that with a <cffile action=&quot;append&quot;> to make a log, and an auto update ability, I think my problems are all solved!

Thanks for all the ideas!