Creating an encrypted copy of an existing database (SQL)

You can create an encrypted copy of a database by using the CREATE ENCRYPTED DATABASE statement. This statement creates a copy of the file (in this case, in encrypted form), and does not overwrite the original database file.

Prerequisites

By default, you must have the SERVER OPERATOR system privilege to execute the CREATE ENCRYPTED DATABASE statement. The required privileges can be changed by using the -gu database server option.

The database you are encrypting must not be running.

Context and remarks

Caution

For strongly encrypted databases, store a copy of the key in a safe location. If you lose the encryption key, there is no way to access the data—even with the assistance of Technical Support. The database must be discarded and you must create a new database.

Task

In Interactive SQL, connect to an existing database, other than the one you are encrypting.
Encrypt the database using the CREATE ENCRYPTED DATABASE statement.

Results

When you execute a CREATE ENCRYPTED DATABASE statement, you do not encrypt (overwrite) the file; you create a copy of the file in encrypted form. If there are transaction logs, transaction log mirrors, or dbspaces associated with the database, encrypted copies of those files are made as well.

Example

The following statement takes the database file temp.db, and creates an AES-encrypted copy of it named encryptedtemp.db.

CREATE ENCRYPTED DATABASE 'C:\temp\encryptedtemp.db'
FROM 'C:\temp\temp.db'
KEY 'abc'
ALGORITHM 'AES';

Creating an encrypted copy of an existing database (SQL)

Caution

Task

See also