Copying Managed Metadata from one farm to another

I was faced with the task of copying managed metadata values from a Test farm to a Production farm. Now I've always deployed Managed Metadata values by using a script before, so I could just deploy it on each farm, but this time I'm taking over someone else's project. My last attempt to google methods of moving Managed Metadata when moving from Dev to Test resulted in failure. I found a tool on Codeplex that purported to do it, but I couldn't make it work. Having already typed all of the metadata in twice (along with synonyms) already, the idea of typing them all in again filled me with dread, especially when moving to production. I debated writing my own tool, but it was half past 10 at night and it needed to be done by the morning. Frankly it would probably take less time to retype them.

Luckily my renewed search attempt was more fruitful and resulted in the following article: http://morshemesh.blogspot.co.uk/2011/12/transferring-managed-metadata-between.html. This was perfect, thank you Mor Shemesh.

... Perfect, except it didn't work. The export PowerShell script ran, but failed to create the backup file. In fact it didn't provide any messages at all (I didn't check the ULS logs). Brilliant. I checked it through and yes, the Proxy was returned correctly, the Service App was returned and the App ID looked correct as well.

It occurred to me at this point that I was running it on the WFE and the Managed Metadata service application was on the App server, so I ran it there. Success, I have a backup file.

So the next step is to run it on the production farm. I logged on to the App server this time rather than the WFE (won't catch me making the same mistake twice), ran the import command and...

You do not have permission to use the bulk load statement

Ok, what does this mean?

Well, googling the error message mostly finds references to SQL Server and tells you that you require the Bulkadmin privilege in SQL Server. Ok fine, I logged in to SQL server and gave myself the Bulkadmin privilege. Still no joy.

It turns out that it's the service application account that requires the privilege. So log on to SharePoint Central Administration and under Security select Configure Service accounts. Select your Managed Metadata service application to find out what account it's running under. Having given this account Bulkadmin privilege in SQL Server and re-run the command, I now have this error:

Import-SPMetadataWebServicePartitionData : Cannot bulk load because the file "C:\ 79A76FF111B34C67BA4E755019FC8347 \ECMGroup.dat" could not be opened.

Ok, this is quite weird, but this page here gave me the answer: http://social.technet.microsoft.com/wiki/contents/articles/5233.export-import-termset-en-us.aspx. Basically I created a network share on the SQL server and gave Everyone read/write access to it. I copied the Managed Metadata backup here and used the UNC path to the file in the PowerShell script. Success at last!

Now I just had to remap my Managed Metadata site columns to point to these newly added ones and was all done by half 11. Time to get some sleep!