CREATE DECRYPTED DATABASE statement

Creates a decrypted copy of an existing database, including all transaction logs and dbspaces.

Syntax

CREATE DECRYPTED DATABASE newfile
FROM oldfile 
[ KEY key ]

Parameters

FROM clause
Use this clause to specify the name of the database to copy (oldfile).
KEY clause
Use this clause to specify the encryption key needed to decrypt the database. You can specify either a string or a variable name for the key. You do not specify the KEY clause if the existing database was encoded with simple obfuscation, which does not require a key.

Remarks

The CREATE DECRYPTED DATABASE statement produces a new database file (newfile), and does not replace or remove the original database file (oldfile).

All encrypted tables in oldfile are not encrypted in newfile, and table encryption is not enabled.

Note For databases created with SQL Anywhere 12 or later, the ISYSCOLSTAT, ISYSUSER, and ISYSEXTERNLOGIN system tables always remain encrypted to protect the data from unauthorized access.

If oldfile uses a transaction log or transaction log mirror, the files are renamed newfile.log and newfile.mlg, respectively.

If oldfile contains dbspace files, a D (decrypted) is added to the file name. For example, when you execute the CREATE DECRYPTED DATABASE statement, the file mydbspace.dbs is changed to mydbspace.dbsD.

If disk sandboxing is enabled, then the database's operations are limited to the directory where the main database file is located.

You cannot execute this statement on a database that requires recovery. This statement is not supported in procedures, triggers, events, or batches.

You cannot be connected to the database you are decrypting. You must be connected to a different database. For example, connect to the utility database. The database that you are encrypting must not be running.

Privileges

Your ability to execute this statement depends on the setting for the -gu database option, and whether you have the SERVER OPERATOR system privilege.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The first statement below creates an AES256-encrypted copy of the demo.db called demoEncrypted.db. The second statement creates a decrypted copy of demoEncrypted.db called demoDecrypted.db.

CREATE ENCRYPTED DATABASE 'demoEncrypted.db'
   FROM 'demo.db'
   KEY 'Sd8f6654*Mnn'
   ALGORITHM 'AES256';
CREATE DECRYPTED DATABASE 'demoDecrypted.db'
   FROM 'demoEncrypted.db'
   KEY 'Sd8f6654*Mnn';