Perform backup and recovery

A rigorous backup regimen is recommended to provide a means to recover both the database and associated keys used for encryption. Consult your corporate IT and security team for best practice and corporate policy requirements.

Back up the Security World
Restore the Security World
Back up the database
Restore the database

Back up the Security World

The Security World data is inherently encrypted and does not require any further encryption operation to protect it. It can only be used by someone who has access to a quorum of the correct ACS cards, or the OCS card, Softcard, their passphrases, an nShield HSM and nShield Security World Software. Therefore, backup simply consists of making a copy of the Security World files and saving the copy in a safe location, as necessary to restore the keys used by the database.

Back up C\:ProgramData\nCipher\Key Management Data.
Securely store and keep a record of ACS and OCS cards associated with each Security World, preferable using the serial number on the cards.
The Softcard, used instead of OCS, resides in the Key Management Data folder. It is backed up at C\:ProgramData\nCipher\Key Management Data.
Keep a record of which database and which Security World backups correspond to each other.

Restore the Security World

Restoring a Security World simply means restoring a backup copy of the Security World folder C\:ProgramData\nCipher\Key Management Data.

The ACS is required if the Security World being restored is not already loaded onto the HSM. See the Installation Guide and the User Guide for the HSM. A short version is available at How to locally set up a new or replacement nShield Connect.

Access to the Entrust nShield Support Portal is available to customers under maintenance. To request an account, contact nshield.support@entrust.com.

Back up the database

To back up the database:

Create the backup devices by running the following query:

-- Encrypted Backup
USE master;
GO

--Provide backup device and locations
EXEC sp_addumpdevice 'disk', 'EncryptedTestDatabaseBackup',
'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDatabaseEncrypted.bak';
GO

EXEC sp_addumpdevice 'disk', 'EncryptedTestDatabaseBackupLog',
'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDatabaseEncryptedLog.bak';
GO

Notice the devices created.

Create the backup by running the following query:

-- Encrypted Backup
USE master;
GO

ALTER DATABASE TestDatabase
SET RECOVERY FULL;
GO	

-- Back up the encrypted database
BACKUP DATABASE TestDatabase TO EncryptedTestDatabaseBackup;
GO

-- Back up the encrypted log
BACKUP LOG TestDatabase TO EncryptedTestDatabaseBackupLog;
GO

Notice the backup files created.

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup>dir
 Volume in drive C has no label.
 Volume Serial Number is CC11-1791

 Directory of C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup

08/16/2023  12:26 PM    <DIR>          .
08/02/2023  11:27 AM    <DIR>          ..
08/16/2023  12:26 PM         4,743,680 TestDatabaseEncrypted.bak
08/16/2023  12:26 PM            86,528 TestDatabaseEncryptedLog.bak
               2 File(s)      4,830,208 bytes
               2 Dir(s)   6,326,734,848 bytes free

If the database is encrypted, the backup will also be encrypted. If the database is not encrypted, then the backup will not be encrypted. If you want to create an encrypted backup from a non-encrypted database, you will have to create the non-encrypted backup file, and then encrypt the file using an independent encryption tool.

Restore the database

Restore a TDE encrypted database in a similar manner as an un-encrypted database. But for TDE encrypted database the Security World needs to be restored before restoring the encrypted database. The OCS, if used, needs to be inserted in the HSM before restoring the encrypted database. Otherwise, the restored database will appear as (Restore Pending).

Install the Security World software and the nShield nDSOP if rebuilding the server. Do not create a Security World.
Restore the Security World.
Insert the OCS in the HSM front panel slot, or the TVD if using OCS protection.
Enable EKM and register the SQLEKM provider if rebuilding the server.
Create the SQL Server credential if rebuilding the server. The OCS and Softcard are in the restored Security World.
Verify the SQLEKM provider configuration if rebuilding the server.
Import the database wrapping key (TDEKEK) into the master database by running the following query. This is the TDEKEK last used to encrypt the database. This key should already exist in the restored Security World.
```
USE master;
GO

-- Import TDEKEK2
CREATE ASYMMETRIC KEY "AsymTestWrappingKeyDatabase2"
FROM PROVIDER "nDSOP"
WITH
PROVIDER_KEY_NAME = 'AsymTestWrappingKeySQLEKM2',
CREATION_DISPOSITION = OPEN_EXISTING;
GO
```

Recreate the TDE login and credential by running the following query. These are the TDE login and credential last used to encrypt the database. Notice the name of the OCS (nDSOPocs), and Softcard (nDSOPsoftcard) created earlier.

OCS:

USE master;
GO

-- tdeLogin2 and tcdCredential2
CREATE LOGIN tdeLogin2 FROM ASYMMETRIC KEY AsymTestWrappingKeyDatabase2;
CREATE CREDENTIAL tdeCredential2 WITH IDENTITY = 'testOCS', SECRET = 'ncipher'
FOR CRYPTOGRAPHIC PROVIDER nDSOP;
ALTER LOGIN tdeLogin2 ADD CREDENTIAL tdeCredential2;
GO

Softcard:

USE master;
GO

-- tdeLogin2 and tdeCredential2
CREATE LOGIN tdeLogin2 FROM ASYMMETRIC KEY AsymTestWrappingKeyDatabase2;
CREATE CREDENTIAL tdeCredential2 WITH IDENTITY = 'testSC', SECRET = 'ncipher'
FOR CRYPTOGRAPHIC PROVIDER nDSOP;
ALTER LOGIN tdeLogin2 ADD CREDENTIAL tdeCredential2;
GO

Restore the database by running the following query:

USE master
RESTORE DATABASE [TestDatabase] FROM  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TestDatabaseEncrypted.bak'
GO

Return to multiple user mode by running the following script:
```
USE master;
ALTER DATABASE TestDatabase SET
MULTI_USER;
GO
```