Restore a SSISDB to another server

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  August 3, 2016 at 5:03 pm

  #315945

  I am try to figure out how to recover a SSISDB catalog and database to another new server.

  I created the key for encryption when I initially create the SSISDB catalog.

  When I start the step following this Microsoft article:

  https://msdn.microsoft.com/en-us/library/hh213291%28v=sql.120%29.aspx

  The first step is to backup the key using below

  backup master key to file = 'c:\SSISDBKEY'

  encryption by password = 'Mypassword'

  But it failed with

  Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

  I am sysadmin, so I have all permissions, but why it gives this error?

  Thanks

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  August 3, 2016 at 5:23 pm

  #1893654

  I figured out it needs to be opened first before backup