Database back-up and restore

It should be part of your corporate disaster recovery policy to perform regular back-ups of both your database and associated Security World such that the back-ups remain up to date and synchronized with each other.

If you are backing up a database that uses cell encryption keys, you should ensure that all sensitive data is encrypted first before back-up commences. Before back-up, remove the cell encryption key references from the database itself. If key references are not removed from the database, they will be stored within the database back-up. This should be avoided from a security point of view. If you are backing up a database that is both cell and TDE encrypted, perform the above instructions for the cell encryption keys before continuing with the following instructions for backing up a TDE encrypted database.

When backing up a TDE encrypted database, you must have the TDE credential (including protection domain) and database wrapping key (TDEKEK) present.

Once you have prepared the database as described above, you may back-up the database in a similar manner to an unencrypted database. If you are backing up a TDE encrypted database, it will be backed up while remaining in its encrypted form, which is advantageous from a security point of view.

Your backup will include data content of your selected database, but may not include backups of SQL Server logins or credentials. Please refer to Microsoft SQL Server documentation for details of how to back these up. Otherwise, when later restoring the database, you may have to recreate suitable SQL Server logins and credentials, although this should not be a difficult task.

Backing up a database with SQL Server Management studio

This provides a basic example of how to backup a database. Please refer to Microsoft SQL Server documentation for a more thorough treatment of backup (and restoration) of a database.

In SQL Server Management Studio, navigate to Management.
Right-click on Management and select Back up.
Set Database_Name using the pull down menu.
Set Backup type as Full using the pull down menu.
Set Backup component button as Database.

Under Destination select Disk.

Click Remove to set aside any previously named back-up file(s) that you do not want to keep. Click Add and provide a suitable path and name for the backup file, e.g. <Drive>:<Backup_directory_path>\TestDatabase_TDE_[date].bak Press OK to accept the file path and name. Press OK again. You must remove the existing entry as backup only allows a single entry to populate this field at any one time. Make sure that you rename with a meaningful and unique name for the backup and include the bak suffix.

When the back-up is complete, the message The backup of database 'TestDatabase' completed successfully is displayed. Press OK.

Make sure you can access the back-up file at the location given above.

If the database back-up fails with a message indicating that the transaction log is not up to date, repeat the above steps, but for step 4 select Backup type as Transaction Log. In step 6, provide a suitable Log file name. After this completes successfully, you should be able to perform the database back-up.

Restoring from a back-up

Restoring a database, including a TDE encrypted database, is similar in manner to an unencrypted database.

Once the database is restored, you will require suitable SQL Server logins and associated credentials to use the database and retrieve keys from the Security World. If these are not already present, or you have not restored them by some independent means, you will need to regenerate them. Once you have created a credential you must associate it with an authorized login. See Creating a credential for details of how to create a credential.

For cell encryption keys, once the database is restored with valid credentials and associated login, you can restore the cell encryption keys from the Web Services SQLEKM provider by reimporting them. But there is no need to do this until you need the keys. You must be using the correct credentials for the particular keys you wish to reimport, see Re-importing symmetric key or Re-importing an asymmetric key.

If you are restoring a database that uses both cell encryption and TDE encryption, then the database must first be restored for TDE encryption as shown below, before reimporting the cell encryption keys.

The following description focusses on restoring a TDE encrypted database. It assumes the database wrapping key (TDEKEK) has not been reimported into the master database.

Before proceeding to restore a TDE encrypted database:

The user will need to use a personal login that is associated through a credential with the same protection domain that is protecting the TDEKEK for the database to be restored. If necessary, create a credential that uses this protection domain, and associate it with the user login.
The database wrapping key (TDEKEK) should already exist and will need to be reimported into your master database using the 'OPEN_EXISTING' clause as in the example below.
```
USE master
CREATE ASYMMETRIC KEY dbAsymWrappingKey
FROM PROVIDER <provider name>
WITH PROVIDER_KEY_NAME='ekmAsymWrappingKey',
CREATION_DISPOSITION = OPEN_EXISTING;
GO
```

You will need to recreate the TDE login and credential that was originally used with the database e.g.

Use master
CREATE LOGIN tdeLogin FROM ASYMMETRIC KEY dbWrappingKey;
CREATE CREDENTIAL tdeCredential WITH IDENTITY = '<protection domain id>', SECRET = '<protection domain passphrase>'
FOR CRYPTOGRAPHIC PROVIDER SQLEKM;
ALTER LOGIN tdeLogin ADD CREDENTIAL tdeCredential;

The user will need to use a personal login that is associated through a credential with the same protection domain that is protecting the TDEKEK for the database to be restored. If necessary, create a credential that uses this protection domain, and associate it with the user login.
After setting up the TDEKEK and credentials above, you may now restore the TDE encrypted database in a similar manner to an unencrypted database. If the database was backed up in an encrypted state, it should be restored in an encrypted state, and you should not need to switch on encryption.