{{last_updated_at}} by {{last_updated_by}}

CiviCRM multisite to single site cookbook¶

• Build an empty copy of CiviCRM which matches the CMS and Civi version.
• Immediately change the CIVICRM_DOMAIN_ID on the new site (in civicrm.settings.php) to the CIVICRM_DOMAIN_ID of the site you're extracting. In my examples below the DOMAIN_ID is 2.
• Also note the CIVICRM_DOMAIN_GROUP_ID from the original site's civicrm.settings.php.
• Create a group made up of all contacts.
• Use the "Include/Exclude by Group/Tag" custom search to find everyone in "All Contacts" but not the CIVICRM_DOMAIN_GROUP.
• You can't delete these contacts through the UI if they have financial data. So add them to a new group "Contacts to Delete" and note the group ID. We'll delete them later.

Before deleting membership types that don't belong to your domain, you have to delete the memberships:

DELETE cm from civicrm_membership cm JOIN civicrm_membership_type cmt ON cm.membership_type_id = cmt.id WHERE cmt.domain_id IS NOT NULL AND cmt.domain_id != 2;
DELETE cmb from civicrm_membership_block cmb JOIN civicrm_membership_type cmt ON cmb.membership_type_default = cmt.id WHERE cmt.domain_id IS NOT NULL AND cmt.domain_id != 2;

I did a search for tables with a "domain_id" column and generated these SQL statements:

DELETE FROM civicrm_mailing_abtest WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_job WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_job_log WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_mail_settings WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_menu WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_navigation WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_option_value WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_uf_match WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_setting WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_word_replacement WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_status_pref WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_payment_processor WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_sms_provider WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_membership_type WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_report_instance WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_price_set WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_dashboard WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_mailing WHERE domain_id IS NOT NULL AND domain_id != 2;
DELETE FROM civicrm_domain WHERE id != 2;

Delete remaining contacts:

DELETE cc from civicrm_contact cc JOIN civicrm_group_contact cgc ON cc.id = cgc.contact_id JOIN civicrm_group cg ON cgc.group_id = cg.id WHERE cg.title = 'contacts to delete';

Contributions aren't domain-specific - so if you have contacts who might have given to multiple domains, it's hard to figure out which are which. One easy solution is to ID the contribution pages (more on that below) that don't belong to your site and delete contributions made through any other page.

DELETE cc from civicrm_contribution cc JOIN civicrm_contribution_page ccp ON cc.contribution_page_id = ccp.id WHERE ccp.id NOT IN (10, 11);
You can often do the same for financial types:
civicrm_contribution

Contribution Pages aren't domain-specific - and the payment processor ID field isn't normalized. However, you can view the payment_processor field to see if their payment processors match those with the wrong domain_id.

Groups aren't domain-specific - delete them manually. You can review title and created_id fields for clues.

delete cc from civicrm_contribution cc JOIN civicrm_financial_type cft ON cc.financial_type_id = cft.id WHERE cft.id IN (2,5,6,7,8,9,10,11,15,16,17);
delete from civicrm_financial_type WHERE id IN (2,5,6,7,8,9,10,11,15,16,17);

Review also:
Custom fields
Profiles
Activity/Relationship types