How to Back Up the Database Master Key

Introduction

The Database Master Key (DMK) is similar to the Service Master Key (SMK). It builds the root of the SQL Server encryption hierarchy on a database level. Loosing access to that key can be equally disastrous as loosing access to the SMK, so just like you should always have a good backup of the SMK, you also need to plan your backup and restore strategy for the DMKs.

BACKUP MASTER KEY Example

The Database Master Key is stored in its database, so it is backed up together with that database. However, it is a best practice to have a separate backup of the DMK. One of the advantages of having a backup of just the DMK is that you can restore it, if for example it was accidentally dropped. Without a separate backup, you would have to restore the entire database, which could potentially take significantly longer and might even cause data loss.

To take a backup of the Database Master Key, you can use the BACKUP MASTER KEY statement.

Note that the word "database" is omitted from this statement to prevent confusion with the BACKUP DATABASE statement.

Like the BACKUP SERVICE MASTER KEY statement, the BACKUP MASTER KEY statement has just two "parameters". The first is part of the TO FILE clause and specifies where the backup will be stored. This file must not exist, as an existing backup file will cause an error. The second is the password that is used to protect the backup file from prying eyes. This should, as always when dealing with encryption keys, be a strong and unique passphrase.

The backup itself should be stored in a secure off-site location. And remember to also safe that passphrase, as without it you cannot restore the key.

Summary

The Database Master Key is the root of the database encryption hierarchy in SQL Server. It potentially can be required to access all your encrypted data. It is therefore important that you have a good backup of each DMK. You can use the BACKUP MASTER KEY statement to create those backups.