It is easy to get this one wrong. SSISDB is just a database, after all, and I’ve seen a number of instances where it was restored to another server and a number of errors/issues crop up. After all, SSISDB is the backend database for the SSIS Catalog. Restoring this database to another server is similar to migrating all the SSIS projects you have deployed to Integration Services for the SQL Server Instance. So what is the proper way to restore SSISDB?
1. Back up the SSISDB from the source SQL Instance.
2. If you know the password for the Database Master Key for SSISDB you can skip this step. If you no longer have the password used for the master key when the SSISDB was created, then back up the master key now:
backup master key to file = ‘C:\MSSQL\SQL_masterkey’ –Replace with the location where you can save it.
encryption by password = ‘REPLACE WITH PASSWORD’ –replace with password
3. If you are restoring the backup to a SQL Server instance where the SSIS Catalog was never configured, you’ll have a number of extra steps to perform. It will be easier if you just enable SSIS catalog on the new server first. You can do that through SSMS. Navigate to the Integration Services Catalog and right click to “Create Catalog.”
4. If you’ve already restored SSISDB on the new server without configuring SSIS Catalog, it will be a bit messed up. Even if you delete the SSISDB the “Create Catalog” will be greyed out because the other items – security, assemblies, etc – weren’t configured correctly. It would be best to force the creation using PowerShell instead of addressing them one by one. You can do this by running this PowerShell script:
# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”)# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”Write-Host “Connecting to server …”
# Create a connection to the server
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create the Integration Services object
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace”.Catalog” ($integrationServices, “SSISDB”, “[email protected]!”) #REPLACE THE PASSWORD
$catalog.Create()
5. This will ensure all the proper permissions are granted, required users are created and all assemblies are registered.
6. Proceed and restore the SSISDB from the backup.
7. If you know the original password for the master key as explained in Step 2, you can go ahead and open the key. If not, and you backed up the master key, restore the master key now.
Script to Open the Master Key:
open master key decryption by password = ‘[email protected]!’ –‘Password used when creating SSISDB’
Alter Master Key Add encryption by Service Master Key
Script to Restore the Master Key:
Restore master key from file = ‘C:\MSSQL\SQL_masterkey’ –Replace with the location of your masterkey backup
Decryption by password = ‘REPLACE WITH PASSWORD USED’ — ‘Password used to encrypt the master key during SSISDB backup’
Encryption by password = ‘REPLACE WITH NEW PASSWORD’ — ‘New Password’
Force
Note: You may receive the warning:
The current master key cannot be decrypted. The error was ignored because the FORCE option was specified
As the warning states, it can be ignored.
8. Map the cleanup user to the cleanup login.
use SSISDB
GoEXEC sp_change_users_login ‘update_one’, ‘##MS_SSISServerCleanupJobUser##’,’##MS_SSISServerCleanupJobLogin##’
9. You can now test if the SSIS Catalog is working properly by checking that your projects are there, by running the packages and/or by trying to deploy a package to the SSIS Catalog. If you are restoring from different SQL Server version, you may want to run catalog.check_schema_version to make sure it is compatible and working.
You may refer to the Microsoft Documentation on SSIS Catalog for more information.
9 Comments. Leave new
Thank you. I was having multiple issues with encryption/decryption error while playing around with restoring SSIDB from 2012 to 2016 but your steps helped me with fixing it.
I followed the instruction to migrate a SSISDB from SQL 2014 to a new server of 2017. and got an error at the last step to verify the version: catalog.check_schema_version.
Msg 10314, Level 16, State 11, Procedure catalog.check_schema_version, Line 117 [Batch Start Line 2]
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly ‘microsoft.sqlserver.integrationservices.server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.RuntimeAssembly._
I fixed this particular error by:
USE SSISDB
ALTER DATABASE SSISDB SET TRUSTWORTHY ON;
EXEC sp_changedbowner ‘sa’;
This also helped me – setting trustworthy on.
Although I don’t believe it’s the correct solution, it works and I’ll keep it that way.
Hi Annec – you need to run the upgrade on the catalogue. Right click on SSISDB Cataologue and click, Database Upgrade – if it is grey you may need to do it on the server.
See: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15#upgrade-the-ssis-catalog-ssisdb
Hi, everything worked perfectly until I tried to upgrade the database where I got this error: ‘The system cannot find the file specified (System)’. However there’s no indicatrion as to which file is missing. ERror details are as follows:
——————————
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)
Any thoughts on what this might be?
Hi Gordon. Depending on SSMS version it may try to look for the database upgrade executable in an incorrect path. Launch the executable yourself: C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe. It may be 130 if it is 2016 instead of 140. Also, do this on the local server where the instance is installed.
Thank you Pia for this wonderful post. Much appreciated.
Hi,
I am trying to migrate SSIS DB from one server to another same SQL version while restoring the master key getting below error message… can you please advise.
The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.
Msg 15208, Level 16, State 1, Line 8
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.