Archive Old Data to Different DB ... ?

[theLowlyContractor]

I desperately need to know if it's possible to physically archive/remove data older than a given date from the current database, and store it in a seperate/backup DB. (perhaps accessible in another instance of sugar, but mostly for pruning and backup).

If you have done anything similar, please let me know... links, suggestions, etc.

I have only recently been introduced to Sugar and it's codebase, etc, and am pressed to do this for the usual reasons. = )

I've been a PHP Developer and part time DBA for some years now, so don't be afraid to speak geek or code.

Thanks for any help/leads/links/advice/etc!

The Lowly Contractor

[julian]

Hey there!

There are probably more elegant solutions, but I'd probably use a simple set of SQL commands to perform the sync. This sounds like a one-way sync, so you'll need to identify which tables you want backed up, then simply filter against the date_modified (or date_created) fields. Note that you should pay attention to <table>_cstm tables (which contain custom field information, if you've defined any custom fields for a module), plus the dozens of relationship tables.

For example, if you just wanted to sync the Contacts module, you'd probably need to pull from all these tables:

accounts_contacts
calls_contacts
contacts
contacts_audit
contacts_bugs
contacts_cases
contacts_cstm
contacts_users
email_addr_bean_rel
email_addresses
emails_beans
emails_email_addr_rel
fields_meta_data
meetings_contacts
opportunities_contacts
...etc...

Note that just about all these tables, even the link tables, have a date_modified field that you can filter against. Let us know what you come up with!

[theLowlyContractor]

Thanks for the help! It looks like a backup/archive of older data might not be the easiest or even a very feasable option at the moment... priorities and all.

Thanks again!

[julian]

How about just backing up the entire database on a regular basis? I'd also investigate MySQL's binary log capabilities-- it'd allow you to roll back to any date, I believe.

[theLowlyContractor]

Well, Backups aren't the issue. The issue is actually removing data from the system that is older than a certain date... for Performance purposes more-so than Archive/Backup.

Tho' I'm looking to maintain the structure, etc, so that an instance of Sugar could be pointed at that particular DB (the archive) and the old data would be viewable.

The best or easiest thing, I know... also not my decision. I'm just "the lowly contractor"... hehehe.