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:
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:
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 password3. 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", "P@ssword!") #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 = 'P@ssword!' --'Password used when creating SSISDB' Alter Master Key Add encryption by Service Master KeyScript 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' ForceNote: 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 Go EXEC 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.
