Sql-server – MSSQL 2016 – Restore SSISDB from MSSQL 2012

sql serversql-server-2012sql-server-2016ssms

On "MSSQL 2016 (SP2-CU11) (KB4527378)" I created empty SSISDB. After that I restored SSISDB from "MSSQL 2012 (SP4) (KB4018073)" to "MSSQL 2016 (SP2-CU11) (KB4527378)" On DB I set owner to sa and version to Compatible Level 2016(130).

On Integration Services I want to do upgrade. I click on Database Upgrade and I get error:

The system cannot find the file specified (System)

Program Location:

   at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
   at System.Diagnostics.Process.Start()
   at Microsoft.SqlServer.IntegrationServices.UITasks.ActionHandler.PerformAction(String actionMoniker, IContext context)

How can I solve this problem?

Some screenshots

enter image description here

enter image description here

I tried to run wizard from disk a I got error:

enter image description here

Best Answer

I've ran into the same problem after restoring the SSISDB database from a lower SQL version to a higher SQL version; I've ran into:

  • SSIDDB Upgrade not working or erroring out on the "sql version doesn't match"
  • Validating packages erroring on customized_logging_levels not existing
  • all other errors one can encounter...

I noticed the newer SSISDB has more tables, views and different stored procedures. However there's a fairly easy solution, although unconventional.

  1. restore the SSISDB on the "new" SQL (already Integration Services Catalog Enabled)
  2. sp_changedbowner 'sa'
  3. open master key decryption by password = 'yourkeyinserthere' Alter Master Key Add encryption by Service Master Key
  4. EXEC sp_change_users_login 'update_one', '##MS_SSISServerCleanupJobUser##','##MS_SSISServerCleanupJobLogin##'
  5. ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 130 (or the one you need)
  6. Install a CU higher then your current one

Installing the CU does the SSISDB upgrade and you can check that by looking at the table internal.catalog_properties. It will have a line "upgrade from".

Also, what if you are already on the latest CU? Well, uninstall your last CU and reinstall it. That works too.

Related Question