I’m by no means an expert in SQL Server encryption. What I do know however, is that the Service Master Key is the top of the encryption chain on an instance. This means that any certificate or key will be encrypted using, in part, the Service Master Key. So if you are using encryption then you absolutely need to backup this key for DR purposes.

Backing up the SMK (Service Master Key) is pretty simple using the BACKUP SERVICE MASTER KEY command:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' 
    ENCRYPTION BY PASSWORD = 'password'

Now you may be thinking “But I’m not using encryption, why should I worry about this?” Well you may be using encryption and not realize it. Setting aside the possibility of someone setting up encryption and not telling you (don’t laugh, I’m betting it’s happened to at least one person reading this) did you know that passwords used in a linked server are encrypted? This means that if you have a disaster and you are using linked servers you had best hope that either the SMK is backed up, or you have all of the passwords used in the linked servers. Even if they are a decade old. (Nope, hasn’t happened to me. Not at all.)

Basically I like to follow the golden rule. No not that one, the other one. “Better safe than sorry.” Back it up, store it with your regular backups (making sure you have the password stored somewhere securely in separate location). Also remember that the SMK can change (for example when you change service accounts) so make sure to take these backups on a regular basis also. That way if you actually need it, you have it.

P.S. The restore command for the SMK is RESTORE SERVICE MASTER KEY.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, disaster recovery, encryption, microsoft sql server