Why do I need to backup the Database Master Key?

Question

When I make a Database Master Key for a database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Long Password'

This page from Microsoft recommends I back it up offsite:

http://msdn.microsoft.com/en-us/library/ms174382.aspx

I've confirmed through testing that the Master Key is stored when you backup the database. I'm already backing up the database, and I'm already storing the password safely. So why do I need to backup the Master Key too?

(As an aside, I am already backing up the Service Master Key and storing offsite).

Answer 1

I primarily use them when I'm moving or copying a database to a new instance of SQL Server. In that case, the DMK in the database will be re-encrypted with the new instance's service master key, but the objects encrypted by the DMK (in your case, the symmetric keys) will not be updated accordingly. You'll need to restore the DMK from a separate backup using the FORCE option to allow the objects to be automatically decrypted.

And yes, for the record: corruption of the keys is rare, and they will be verified by the same mechanisms as any other data in the database (i.e., CHECKDB). However, I would still keep backups of all encryption keys you use - master keys, certificates, symmetric keys, etc. In all of those cases, if you lose the key, you will never be able to decrypt the associated data. It's not necessary to backup the keys with every database backup, but it's good insurance to back them up when they're created or modified.

Answer 2

From the documentation Back Up a Database Master Key

The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.

So, if the master key has gotten corrupted during the database backup, storage, or the restore, then the restored encrypted data, including other keys/certificates from the backup may be unrecoverable, along with any data that they were used to encrypt, unless you can supply the original, un-corrupted master key.