SQL Storage Compress uses key files when:

• restoring from encrypted SQL HyperBac backups (*.hbe)
• creating compressed and encrypted databases (with the file extensions *.mdfe, *.ndfe and *.ldfe).

Key files are stored in the keys directory of the SQL Server that SQL Storage Compress is installed on. By default this is%ProgramFiles(x86)%\Red Gate\HyperBac\keys on 64-bit machines and %ProgramFiles%\Red Gate\HyperBac\keys on 32-bit machines.

The key file is equivalent to a password for accessing encrypted data. If you lose the key file associated with an encrypted backup file or encrypted database files, the data cannot be accessed or recovered.

Make sure you have a backup strategy for key files, and that key files are backed up and stored separately from the data they have been used to encrypt.

Restoring from encrypted SQL HyperBac backups

When SQL Storage Compress restores from a SQL HyperBac backup (*.hbe) it searches the keys file on the server for the key used to encrypt the backup. If the backup was created on a different server, the relevant key file will be stored in the keysdirectory on that server. In this case you can either:

• Copy the key file to the keys directory on the server you are restoring to, then restore a database from the backup. After the restore, remove the key file to prevent it being used for other encryption operations on that server.

  If a key file of the same name already exists on the server you are restoring to (by default, AES_256.key), temporarily move the existing key file to another location. Once you have completed the restore, remove the key file used to decrypt the backup and return the original key file to the keys directory.

  Ensure that no SQL HyperBac operation that use encryption are in progress on either server before moving the key files.

or

• Extract the SQL HyperBac backup to an unencrypted format, such as .bak. You can use HyperBac WinExtractor or HyperUtil.exe to do this. The key file used to encrypt the backup must be available in the keys directory of the server on which you run WinExtractor or HyperUtil.exe. You can then restore a database from the unencrypted backup. This is useful if you want to restore from a backup to encrypted database files (*.mdfe, *.ndfe and *.ldfe) on a different server. See Working with HyperBac WinExtractor or Working with HyperUtil.exe for more information.

Creating encrypted database files

When you restore a database to encrypted database files (*.mdfe, *.ndfe and *.ldfe), SQL Storage Compress first checks the keys directory for existing key files. If an AES_256.key file already exists, SQL Storage Compress uses it to encrypt the database files. If a key file does not exist, SQL Storage Compress will generate a key file and store it in the keys directory.

Accessing encrypted database files

When you read from or write to encrypted database files, SQL Storage Compress searches the keys directory for the key file to decrypt or encrypt the data. If the correct key file is not available, SQL Server will report the following error:

Msg 823, Level 24, State 2, Line 2

The operating system returned error 38 to SQL Server during a read at offset <offset> in file <database file path>.

Make sure the key file is stored in the keys directory that SQL Storage Compress is searching.