This blog post is as much for my aging memory as it is for you. I hope you find it useful. And I hope my future self can make sense of this.
Every once in awhile we run into something in ExpressionEngine that we wish could be a bit easier. Yesterday I ran into one of those things when I tried to extract a site out of an MSM install. The client had 6 sites in MSM which I am sure seemed like a good idea at the time. But one of the sites is for a business venture that now requires the site be removed from the MSM install and set free to be on it's own.
Now most of you that have not worked with MSM may not understand why this is a pain. Well, MSM requires that a site, any of the installed sites, be primary. Each site is assigned a numerical value in the database. This is found in the form of a Site ID (site_id). What MSM does not allow you to do is change which site is the primary. So, if you have an MSM install and you need a site from within the install to be removed then you run into a situation where you can delete all sites except for two, the one that is primary and the one that you need. In order to fully remove all unnecessary sites from the install you have to go into the database table by table and look for site_id in all of the various tables. You will then run some SQL commands that will remove the items associated with the unnecessary primary site and move the items that are part of the secondary site into the primary slot. So let's begin.
To begin with make a backup of the site and move it to a secondary host or set it up on your localhost. EllisLab has great instructions for moving a site. I would just add that I use Deeploy Helper on almost every install to make changing paths and URLs easy. So get the site set up.
Make a backup.
Once you have installed the site in a new location and are confident that all of the paths are correct log into the CP. You wil go to the Edit Sites section of the MSM install. Delete all of the sites that you do not want. You will probably find that the site that you do want is not the primary (that's just the way this always ends up) so leave it and the primary.
Make a backup.
Log into PHPmyAdmin or (better yet) Sequel Pro and gain access to the database. You will go table by table and execute the next two commands. I will explain them and then you will use them as necessary. When you select a table you are looking for the following (Snapshot taken from Sequel Pro).
In this image you see on the left that I have the exp_channel_data table selected. You will also notice that the second column in the table is site_id. That is what you are looking for, site_id. If you see that in a table then you will do the following.
This next SQL command will delete the entries in the table that have to do with the current unnecessary primary site (site_id = 1). If you need the primary site then this whole blog post is not for you. So open up the interface in your database management tool that allows you to execute SQL commands and use the following. Make sure to change to the actual table name.
DELETE FROM WHERE site_id = 1
Then after that is done you should move the items that are associate to the secondary site (site_id = 2) into the primary position (site_id = 1). To do that you just use the following. Again, make sure to replace with the actual table name. What this command does is find all of the entries in the database that have a site_id that is equal to 2 and changes them so that they are equal to 1.
UPDATE SET site_id = '1' WHERE site_id = '2'
That's it. Just rinse and repeat over and over again.
I did send a quick email to Derek Jones about this and he is aware of the issues. I believe (I am inferring from his email) that the concern is that in large EE sites if they (EllisLab) make it easy to switch primary then the DB could easily become overwhelmed due to the amount of updates that are necessary. But he did ask for the use case. Not sure what he will do but I hope that in the not too distant future we will get some sort of easy way to extract a site out of MSM.