After the source server crashed, and assuming that I have the ssisdb full backup and password, what are the steps to configure and restore the ssisdb/packages/permissions onto a new server?

Here is what I tried

  1. Restored system dbs
  2. After restoring master db I can see that the SSIS db is in recovery pending state and the create catalog option when right click on integration services catalog is disabled.
  3. If I delete the SSISDB, then the above option gets enabled. Upon clicking the create catalog and entering the details and OK I get an error: An error occurred during service master key decryption

How to proceed from here?

  • please follow this: sqlshack.com/…
    – MBuschi
    May 11 at 7:57
  • Ok but the step 2 can't be run assuming the original sql server has crashed: backup master key to file = 'c:\temp\SQLNode1MasterKey' encryption by password = 'P@ssw0rd' - is there an alternative?
    – variable
    May 11 at 13:15
  • You have to replace the encryption key with a new one. I think you are going to lose encrypted content in the database like connection strings or sensitive parameters.
    – MBuschi
    May 11 at 14:50
  • Could you point me to any article that clarifies the procedure please
    – variable
    May 11 at 15:49
  • You should be able to regenerate it in this way: USE [SSISDB]; GO ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD =N'YourPassword'; docs.microsoft.com/en-us/sql/t-sql/statements/…
    – MBuschi
    May 12 at 6:47


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.