SharePoint 2010: how to import/export metadata terms

Using PowerShell script to achieve this

  • Service account required to have BULKAdmin permission in SQL database, otherwise it will be having exception: You do not have permission to use the bulk load statement
  • Make sure the service account will have the permission to access to the file path, otherwise it will be having exception:  Access to the path ‘C:\Temp’ is denied.

Here is the PowerShell Script to Import and Export terms:

#Update these according to what is created/shown in central administration

$proxyName = “Managed Metadata Service”
$serviceName = “Managed Metadata Service”
$filePath = “C:\Temp\terms.bak”

#Get the Metadata Service Application

$svc = Get-SPServiceApplication | ?{$_.TypeName -eq “Managed Metadata Service” -and $_.DisplayName -eq $serviceName}

#Use this to Export

Export-SPMetadataWebServicePartitionData $svc.Id -ServiceProxy $proxyName -Path $filePath

#Use this to Import

Import-SPMetadataWebServicePartitionData $svc.Id -ServiceProxy $proxyName -Path $filePath -OverwriteExisting

This only works in a single farm , all in one box solution, there will be issues coming up when it’s a multi-server situation.

You will get the following exception if that’s the case:

Cannot bulk load because the file “C:\Temp\terms.bak” could not be opened.

The workaround for this is:

  1. copy the terms.bak to the SQL server box,  and grant read and write permission with the service account in that folder temporarily
  2. Replace the file path to use a network path, for example “\\servername\c$\temp\terms.bak”

Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’

Resolution:

  1. Run regedit.exe
  2. Navigate to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Setup
  3. Note down the path of SQLDataRoot, example: C:\Program Files\Microsoft SQL Server\MSSQL10_50.<SQLInstanceName>\MSSQL
  4. Navigate to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<SQLInstanceName>\MSSQLServer
  5. Verify if the path in DefaultData/DefaultLog looks similar to the path in SQLDataRoot
  6. DefaultData path should be similar as: C:\Program Files\Microsoft SQL Server\MSSQL10_50.<SQLInstanceName>\MSSQL\DATA
  7. DefaultLog path should be similar as: C:\Program Files\Microsoft SQL Server\MSSQL10_50.<SQLInstanceName>\MSSQL\Log
  8. Update these path if it’s invalid
  9. Restart the SQL Server Agent if necessary
  10. Problem solve

*Replace <SQLInstanceName> with your database instance name