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”

Unable to discover SQL server on the network but able to connect to it?

Scenario: I can be able to connect to the SQL server but I can’t discover the Server in other server?

  1. Open up SQL Server Configuration Manager
  2. Navigate to SQL Server Services
  3. Verify that SQL Server Browser is enabled
  4. If it is disabled
  5.  Right click on SQL Server Browser
  6. Navigate to the Service Tab
  7. Change Start Mode to Automatic
  8. Start SQL Server Browser


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


  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