Create an Empty Database Using SQL Server Integration Services

• Nisarg Upadhyay

  SSC-Addicted

  Points: 495

  More actions

  April 17, 2023 at 12:00 am

  #4171918

  Comments posted to this topic are about the item Create an Empty Database Using SQL Server Integration Services

• JoeMoyle1

  SSC Enthusiast

  Points: 117

  More actions

  April 19, 2023 at 7:13 pm

  #4176441

  Hi Nisarg and anyone else interested.  I tried this and it creates a nice empty database.  Thanks for the instructions.  But I still find a big problem with it that revolves around encryption/decryption.  I find that I can't use the decryption procedures on this database despite recreating the master key, certificate, and symmetric keys.  They only work if I restore the original whole database.  I can restore it with a different name or whatever but decrypting works fine from the restored database but not the new empty copy created.  Any ideas regarding how to get a call like the following to work on the empty database.

  OPEN SYMMETRIC KEY FsaKey DECRYPTION BY CERTIFICATE FsaCert;

  SET @clearText = DecryptByKey(@cipherText);

  SELECT @clearText OUTPUT;

  CLOSE SYMMETRIC KEY FsaKey;