SharePoint 2013: how to restore a managed metadata database

Print this posting

Introduction

This posting walks through the steps for restoring a Managed Metadata Service Application (MMSA) database. I use this process to "refresh" a development farm with the latest MMSA changes. The steps in brief are:

1. Stop service
2. Detach old database
3. Attach backup database
4. Grant farm service accounts access: Admin, farm, AppService
5. Configure MMSA for new database
6. Start MMSA
7. Start MM service
8. Check App pools and IIS web services root
9. Reset IIS

Procedure

1. Log into a farm server using the SharePoint Setup User Administrator account.
2. Launch a SharePoint Management Shell as Administrator (right-click).
3. Execute the following: Get-SPServiceInstance | Where {$_.Name -Like "*Managed*"}. This will return all instances of the service application - one for each SharePoint server in your farm. not all of these instances will be running.
   
4. Copy the GUID for the running instance.
5. Execute the following: Stop-SPServiceInstance -Identity <ServiceGUID>.  This will stop, or unprovision, the MMSA instance.
   
6. Leave the shell and this login session open for the time being (if you can).
7. Log into the farm database server host using a farm administrator account.
8. Copy the backup MMSA database to this server.
9. Launch SQL Server Management Studio (SSMS) as Administrator.
10. In Object Explorer, expand the tree to the farm's Managed Metadata database.
11. Right-click on the database, point to Tasks, and then click Detach...
12. Enable the Drop Connections and Update Statistics options, then click OK.
13. Navigate to the folder containing the MMSA database data file.  By default, it will be: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA.
14. Rename the MMSA database file to something else.
15. Repeat for the MMSA database log file.

    NOTE: steps 12-14 are performed on the assumption that the restored MMSA database has the same database name and filename as the one that you are removing.  If this is not the case, skip these steps.

16. In SSMS, in Object Explorer, right-click on the Databases node, and then click Restore Database.
17. Perform the usual steps for restoring a database, in this instance, the backup MMSA database.
18. Configure farm service accounts for DBO database access:
    1. SharePoint Setup User Administrator (spAdmin)
    2. SharePoint Farm Service (spFarm)
    3. SharePoint Application Service (spService)
    4. Other farm administrator accounts as needed
    Failure to configure these accounts with their usual access (the access they had before) will manifest as counter-intuitive error messages seen when trying to view the properties of the Management Metadata Service Connection, 
    
    or trying to view the Term Store Management Tool,
    
19. Close SSMS.
20. Return to the SharePoint Server and the open shell session.
21. Execute this script:
    

    $app = Get-SPServiceApplication -Name "<service application name>"
    Set-SPMetadataServiceApplication -Identity $app -DatabaseName "<dbname>"
    Start-SPServiceInstance -Identity 

replacing brackets with appropriate values.

NOTE: the <service application name> above is not the one that you see when you execute Get-SPServiceInstance, but the one you see in Manage service applications and what you see when you execute Get-SPServiceApplication.

22. Navigate to: System Settings > Manage services on server.
23. Verify that the Managed Metadata Web Service is started on the appropriate server.  If it isn't, start it.
24. Launch IIS Manager.
25. In the Connections panel, select Application Pools.
26. Verify that the SharePoint Web Services Root is started.  If it isn't, start it.
27. Now expand Sites, and then select SharePoint Web Services.
28. Verify that it is started.  If it isn't, start it.
29. Open an elevated Command Shell.
30. Execute IISReset.
31. Test by first navigating to: Central Administration > Application Management > Manage Service Applications.
32. Then select Managed Metadata Service, and then select the Manage button.  The Term Store Management Tool should display without issue.  If not review the steps performed here and ensure that you have completed all of them.

References

• SharePoint 2013: How to Restore Content Databases
• Move the Managed Metadata Service service application databases in SharePoint Server 2013
• Start or stop a service (SharePoint 2013)
• There are no addresses available for this application" error in SharePoint managed metadata service

Notes

• Managed Metadata Service Application runs on a single server, in this case a single application server.