dump and load work on the ciphertext of encrypted columns. This behavior ensures that the data for encrypted columns remains encrypted while on disk. dump and load pertain to the whole database. Default keys and keys created in the same database are dumped and loaded along with the data to which they pertain.
If the loading database contains encryption keys used in other databases, load does not succeed unless the new syntax with override is used.
load database key_db from "/tmp/key_db.dat" with override
If your keys are in a separate database from the columns they encrypt, Sybase recommends that:
When you dump the database containing encrypted columns, you also dump the database where the key was created. You must do this if you have added new keys since the last dump.
When you dump the database containing an encryption key, dump all databases containing columns encrypted with that key. This keeps the encrypted data in sync with the available keys.
After loading the database containing the encryption keys and the database containing the encrypted columns, bring both databases online at the same time.
If you load the database containing the keys into a different-named database, errors result when you access the encrypted columns in other databases. To change the database name of the keys’ database:
Before dumping the database containing the encrypted columns, use alter table to decrypt the data.
Dump the databases containing keys and encrypted columns.
After loading the databases, use alter table to reencrypt the data with the keys in the newly-named database.
WARNING! The consistency issues between encryption keys and encrypted columns are similar to those for cross-database referential integrity. See “Cross-database constraints and loading databases” in Chapter 12 of the Adaptive Server Enterprise System Administration Guide: Volume One.
