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
- Restored system dbs
- 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. - 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?
backup master key to file = 'c:\temp\SQLNode1MasterKey' encryption by password = 'P@ssw0rd'
- is there an alternative?