Clean Database

SuiteCRM Forum - English Language Installation & Upgrade Help
1

Hi everyone.

I have a database with custom fields, custom layouts and i need to copy that database but delete all information inside.

But i can’t loose fields and layouts.

I already tried to delete database and create a new one. After that do a quick repair. But i have no luck.

I tried too truncate some table that i know, but know i have errors “This record may be deleted or you may not be authorizade to view it” when i click to create contacts or accounts and do not save but click in cancel.

Which is the better way to do that?

Thanks.

2

You need to preserve the fields_meta_data and custom_fields table for custom fields, the config table for the settings.

Also:
You may want to preserve the tables that begin with user, securitygroup, acl, config, and sugarfeed. As they hold the user specific information and Access rights to records.

The currencies table holds the currency information. So unless you want to re-enter that information - you may want to preserve this table too.

I hope that helps.

3

I know, 6 years after the orignal post and I am thinking you still care … :slight_smile:

I have the same requirement as @qualitividade ; I have finished development of a new system and to test it I added test data.

I now want to put the sytem into production and want to delete the test data - but NOT mess with the structure setup for the system.

If I go through and use the SuiteCRM GUI to “delete” the test entries, the entries still remain in the database - which I do not want; I used real emails, … in the test data so want it fully removed.

Questions:

  1. If I use the SuiteCRM GUI and “delete” the entries and then activate the “Prune database” in the scheduler, will that remove the entries from the database?
  2. If instead of the above, I use sql commands to empty (truncate) tables by looping through the tables of the database (see below for my understanding from above of what tables to NOT truncate) will that cause my system any problems?

As I understand it, the tables I should NOT truncate would be:

  • fields_meta_data - or I will lose the field definitions for custom fields - the ones the system creates (jjwg, …) and the ones I created
  • config - or I lose the Wizard (+) settings and corrupt the system (these do need to be customized for teh new system)

as well as tables that BEGIN with

  • user - Or I will have no way to login to the new system (this does neeed to be customized for the new system)
  • securitygroup - or all the security group and role settings will be lost
  • acl - I assume this is part of the securitygroup issue, setting access control list parameter
    config - see above

I do not understand the use of or need to protect:

  • sugarfeed - seems to contain information on existing users which I wanted deleted
  • custom_fields - this one is empty on my installation so not sure what it is supposed to contain

So, remaining questions:

  1. Is my understanding of the tables to protect correct (and what about the last two)?
  2. If I use the SuiteCRM GUI and “delete” the entries and then activate the “Prune database” in the scheduler, will that remove the entries from the database?
  3. If instead of the above, I use sql cmmands to empty (truncate) tables by looping through the tables of the database (see below for my understanding from above of what tables to NOT truncate) will that cause my system any problems?