How to restore SSISDB to another server and migrate the SSIS catalog

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

# 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", "P@ssword!") #REPLACE THE PASSWORD $catalog.Create()

open master key decryption by password = 'P@ssword!' --'Password used when creating SSISDB' Alter Master Key Add encryption by Service 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

use SSISDB Go EXEC sp_change_users_login 'update_one', '##MS_SSISServerCleanupJobUser##','##MS_SSISServerCleanupJobLogin##'