In my previous article Backup Encryption in SQL Server 2014 Part 1, I have shown backup encryption steps.
In the current article, I will show you how to manage keys.
How to backup a master key?
BACKUP MASTER KEY TO FILE = ‘path_to_file’
ENCRYPTION BY PASSWORD = ‘password’
Let’s backup master key that we have created in our test database. You can specify your secured network share path. In this demo I am saving on my local drive.
— Backup master key.
BACKUP MASTER KEY TO FILE = ‘C:\backups\Keybackups\mymasterkeybackup.key’
ENCRYPTION BY PASSWORD = ‘Password01!’
Now backup certificate.
For syntax explanation please refer to msdn help.
BACKUP CERTIFICATE certname TO FILE = ‘path_to_file’ [ WITH PRIVATE KEY ( FILE = ‘path_to_private_key_file’ , ENCRYPTION BY PASSWORD = ‘encryption_password’ [ , DECRYPTION BY PASSWORD = ‘decryption_password’ ] ) ]
— Backup certificate
BACKUP CERTIFICATE Backup_Certficate_mydatabases TO FILE = ‘C:\backups\Keybackups\Backup_Certficate_mydatabases.cer’
WITH PRIVATE KEY ( FILE = ‘C:\backups\Keybackups\Backup_Certficate_mydatabases_private.key’ ,
ENCRYPTION BY PASSWORD = ‘Password01!’ );
Note: backup location could be unc or network share. Before you place backup files on the folder ask your security team to review.
In my next article I will explain how to restore keys.