Providing a Backup Without Sensitive Data

Overview

You may, on occasion, be required to provide a backup of your on-site instance to SugarCRM Support in order for our team to troubleshoot possible issues. System administrators for on-site instances are responsible for creating these backups as doing so is not supported by SugarCRM. This article will provide instructions on how to create a backup of your on-site instance without including any potentially sensitive data using MySQL commands as an example.

Prerequisites

You must have full administrator access to the database server and the web server that contains the files.

Steps to Complete

There are two main steps required for backing up your on-site environment without including any sensitive data. The first step is to execute the database backup, and then you must execute the filesystem backup.

Executing the Database Backup

In order to include all necessary custom modules and/or relationships, you are required to supply a full backup of the structure of your database.

MySQL Database

For MySQL, this backup can be made by using the mysqldump command with the following parameters to ensure no data is included:

mysqldump -h DB_HOST -u DB_USERNAME -p -d DB_NAME | gzip > YYYYMMDD_DB_NAME.sql.gz

The resulting file YYYYMMDD_DB_NAME.sql.gz will be a compressed (GZip) dump of your database structure of the database specified in DB_NAME.
Note: -d is a short version for --no-data in the mysqldump command.

Microsoft SQL (MSSQL) Database

For MSSQL, this backup can be made by using the sqlcmd command with the following parameters to ensure no data is included:

-- Use SQLCMD or SSMS
sqlcmd -S DB_HOST -U DB_USERNAME -P YOUR_PASSWORD -Q "EXEC sp_msforeachtable 'PRINT ''DROP TABLE ?''; SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(''?'');'" > YYYYMMDD_DB_NAME.struct.sql
-- Or use SQL Server Management Studio / tasks: generate scripts → schema only
-- Then compress:
gzip YYYYMMDD_DB_NAME.struct.sql

The resulting file YYYYMMDD_DB_NAME.sql.gz will be a compressed (GZip) dump of your database structure of the database.

Oracle Database

For Oracle, this backup can be made by using the expdb command with the following parameters to ensure no data is included:

expdp DB_USERNAME/YOUR_PASSWORD@DB_HOST:PORT/DB_SID schemas=YOUR_SCHEMA content=METADATA_ONLY directory=DATA_PUMP_DIR dumpfile=YYYYMMDD_DB_NAME_struct.dmp logfile=YYYYMMDD_DB_NAME_struct.log
# Then optionally convert the dump to SQL if needed, or keep as dump.
gzip YYYYMMDD_DB_NAME_struct.dmp

The resulting file YYYYMMDD_DB_NAME.sql.gz will be a compressed (GZip) dump of your database structure of the database specified in YOUR_SCHEMA.

DB2 Database

For DB2, this backup can be made by using the db2 export command with the following parameters to ensure no data is included:

db2 connect to DB_NAME user DB_USERNAME using YOUR_PASSWORD
db2 export to YYYYMMDD_DB_NAME.struct.del of del select * from syscat.tables where type='T' and tabschema not like 'SYS%' with headers
db2 -x "select 'CREATE TABLE ' || trim(tabschema) || '.' || trim(tabname) || ' (' ||
        listagg(colname || ' ' || typename, ', ') within group(order by colno) || ');'
        from syscat.columns
        where tabschema not like 'SYS%'
        group by tabschema, tabname;" > YYYYMMDD_DB_NAME.struct.sql
# gzip or compress as desired
gzip YYYYMMDD_DB_NAME.struct.sql

The resulting file YYYYMMDD_DB_NAME.sql.gz will be a compressed (GZip) dump of your database structure of the database specified in DB_NAME.

Tables to be Included in the backup

You must also provide the following tables and their content. These tables contain data that is only used by the application, and we must have their data included in the backup in order to recreate a similar copy of your instance for debugging purposes.

acl_actions
acl_fields
acl_roles
acl_roles_actions
acl_role_sets
acl_role_sets_acl_roles
acl_roles_users
config
currencies
custom_fields
eapm
expressions
fields_meta_data
relationships
roles
roles_modules
roles_users
team_memberships
team_notices
team_sets
team_sets_modules
team_sets_teams
teams
upgrade_history
user_preferences
users

MySQL

The following MySQL command may be used to extract these tables including their data:

mysqldump -h DB_HOST -u DB_USERNAME -p DB_NAME acl_actions acl_fields acl_roles acl_roles_actions acl_roles_users config currencies custom_fields eapm expressions fields_meta_data relationships roles roles_modules roles_users team_memberships team_notices team_sets team_sets_modules team_sets_teams teams upgrade_history user_preferences users | gzip > YYYYMMDD_DB_NAME_tablesWithData.sql.gz

Microsoft SQL (MSSQL)

The following Microsoft SQL (MSSQL) command may be used to extract these tables including their data:

-- For each table you want:
sqlcmd -S DB_HOST -U DB_USERNAME -P YOUR_PASSWORD -Q "SELECT * FROM ACL_ACTIONS" -o YYYYMMDD_DB_NAME_ACL_ACTIONS.csv -W -w 1024
sqlcmd -S DB_HOST -U DB_USERNAME -P YOUR_PASSWORD -Q "SELECT * FROM ACL_FIELDS" -o YYYYMMDD_DB_NAME_ACL_FIELDS.csv -W -w 1024
-- etc. for the list of tables: ACL_ROLES, ACL_ROLES_ACTIONS, ACL_ROLE_SETS, ACL_ROLE_SETS_ACL_ROLES,
-- ACL_ROLES_USERS, CONFIG, CURRENCIES, CUSTOM_FIELDS, EAPM, EXPRESSIONS, FIELDS_META_DATA,
-- RELATIONSHIPS, ROLES, ROLES_MODULES, ROLES_USERS, TEAM_MEMBERSHIPS, TEAM_NOTICES,
-- TEAM_SETS, TEAM_SETS_MODULES, TEAM_SETS_TEAMS, TEAMS, UPGRADE_HISTORY, USER_PREFERENCES, USERS
-- Then compress:
gzip YYYYMMDD_DB_NAME_*.csv

Oracle

The following Oracle command may be used to extract these tables including their data:

expdp DB_USERNAME/YOUR_PASSWORD@DB_HOST:PORT/DB_SID tables=ACL_ACTIONS,ACL_FIELDS,ACL_ROLES,ACL_ROLES_ACTIONS,ACL_ROLE_SETS,ACL_ROLE_SETS_ACL_ROLES,ACL_ROLES_USERS,CONFIG,CURRENCIES,CUSTOM_FIELDS,EAPM,EXPRESSIONS,FIELDS_META_DATA,RELATIONSHIPS,ROLES,ROLES_MODULES,ROLES_USERS,TEAM_MEMBERSHIPS,TEAM_NOTICES,TEAM_SETS,TEAM_SETS_MODULES,TEAM_SETS_TEAMS,TEAMS,UPGRADE_HISTORY,USER_PREFERENCES,USERS directory=DATA_PUMP_DIR dumpfile=YYYYMMDD_DB_NAME_selected.dmp logfile=YYYYMMDD_DB_NAME_selected.log
gzip YYYYMMDD_DB_NAME_selected.dmp

DB2

The following DB2 command may be used to extract these tables including their data:

db2 connect to DB_NAME user DB_USERNAME using YOUR_PASSWORD
db2 "EXPORT TO YYYYMMDD_DB_NAME.selected_data.del OF DEL SELECT * FROM ACL_ACTIONS"
db2 "EXPORT TO YYYYMMDD_DB_NAME.selected_data2.del OF DEL SELECT * FROM ACL_FIELDS"
# ... repeat for: ACL_ROLES, ACL_ROLES_ACTIONS, ACL_ROLE_SETS, ACL_ROLE_SETS_ACL_ROLES,
# ACL_ROLES_USERS, CONFIG, CURRENCIES, CUSTOM_FIELDS, EAPM, EXPRESSIONS, FIELDS_META_DATA,
# RELATIONSHIPS, ROLES, ROLES_MODULES, ROLES_USERS, TEAM_MEMBERSHIPS, TEAM_NOTICES,
# TEAM_SETS, TEAM_SETS_MODULES, TEAM_SETS_TEAMS, TEAMS, UPGRADE_HISTORY, USER_PREFERENCES, USERS
# You could also script a loop.
gzip *.del

Executing the Filesystem Backup

After you have executed the database backup, you will then need to execute the filesystem backup. All the files in the Sugar filesystem must be supplied to the SugarCRM Support team in order for the system to be accurately installed and debugged. However, to exclude any potentially sensitive data, please remove from the ./upload/ directory any files whose name starts with a record ID which are similar in structure to "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee". The ./upload/upgrade/ directory must be included in the backup as it is needed for any customizations uploaded to your instance, but other files in ./upload/ should be removed.

Application

Once completed, you will have a backup of your on-site environment clear of any sensitive data. You can safely provide this backup to SugarCRM Support for proper debugging. It is recommended that you supply the files via a compressed archive. When requesting a backup for troubleshooting, SugarCRM Support will provide you with access to an FTP for transferring the backup files.