Decrypting a database (SQL)

You can decrypt a database using the CREATE DECRYPTED DATABASE statement. This statement creates a copy of the file (in decrypted form) and does not overwrite the original database file.

Prerequisites

By default, you must have the SERVER OPERATOR system privilege to execute the CREATE DECRYPTED TABLE 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

If you have a database that requires recovery and you want to decrypt it to send it to Technical Support, you must use the CREATE DECRYPTED FILE statement. Any database-related files such as transaction logs and transaction log mirrors, and dbspace files, must also be decrypted using this statement.

Task

In Interactive SQL, connect to a database other than the one you want to decrypt.
Execute a CREATE DECRYPTED DATABASE statement.

Results

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

Example

The first statement creates an AES256-encrypted copy of the temp.db database called encryptedtemp.db. The second statement creates a decrypted copy of encryptedtemp.db called decryptedtemp.db.

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

Decrypting a database (SQL)

Task

See also