Why do I need to backup the Database Master Key?

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).

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.

StackExchange Database Administrators Q#73926, answer score: 4