January 8, 2014

SharePoint: Manually upgrade Service Application database

Task

After upgrading from SharePoint 2010 to SharePoint 2013, I didn’t need to upgrade all possible Service Applications, but Managed Metadata was one I needed to upgrade. Following TechNet instructions and trying to create new Managed Metadata Service Application using the old SP2010 database never completer, the PowerShell command just stuck and never finished – also the MM Service Application was stuck in Stopped state and couldn’t be started with $mms.Provision().

So, I created fresh Managed Metadata SA, and restored the old database over the new one, after which the Database Upgrade Status page showed that the database would need to be upgraded, how to do that? First guess was $db.Upgrade(), but that wasn’t it.

Solution

In order to upgrade the database, you need to provision it. So, in PowerShell do the following:
  1. Get a list of databases: Get-SPDatabase
  2. Get a hold of the database you want to upgrade: $db = Get-SPDatabase <GUID OF THE DATABASE FROM PREVIOUS STEP>
  3. Finally do the upgrade: $db.Provision()
All done!

7 comments:

  1. I tried using this to upgrade my SearchLinksDatabase, SearchAdminDatabase, SearchGathererDatabase and SearchAnalyticsReportingDatabase databases but it I get the following error:
    Exception calling "Provision" with "0" argument(s):
    "Search_Service_Application_LinksStoreDB_5970c6564d124d11a7666bac8b502c31 on
    sp13be contains user-defined schema. Databases must be empty before they can
    be used. Delete all of the tables, stored procedures and other objects or use
    a different database."

    Any idea how I can upgrade these databases please?

    ReplyDelete
  2. Having the same issue with this command.

    ReplyDelete
  3. Yes, same here. One 3 of 4 SP server, all 3 Reporting Services databases are reporting Database is in compatibility range and upgrade is recommended. When I run get-spdatabase, only the tempDB is returned.

    Exception calling "Provision" with "0" argument(s):
    "ReportingServices_TempDB on spsql contains user-defined schema.
    Databases must be empty before they can be used. Delete all of the tables,
    stored procedures and other objects or use a different database."
    At line:1 char:1
    + $db.provision()
    + ~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SPUncleanDatabaseException

    ReplyDelete
    Replies
    1. You cannot use this command to Provision SQL Server Reporting Services databases. It can only be used for SharePoint Service Application databases.

      Delete
  4. And what is the solution for this problem?

    ReplyDelete
  5. Hi, here’s steps on how to move an SSRS install from 2013 farm to farm, might work for moving from 2013 to 2016, as the plumbing isn’t that different http://anothersharepointblog.com/migrating-a-sql-server-reporting-service-application

    ReplyDelete