0

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?

Improve this question
6
  • 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

0

Reset to default

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.