How to Back Up the Service Master Key

Introduction

A few weeks ago I introduced the SQL Server Service Master Key (SMK). As you can read there, the SMK is the root of the encryption hierarchy and potentially is the only key to a lot of your data. As such, it deserves special attention when it comes to your Disaster Recovery Plan.

The BACKUP SERVICE MASTER KEY Statement

SQL Server already stores multiple copies of the SMK in the master database, but it is still a good idea to have a separate backup of it. In fact, Books Online says: "The service master key should be backed up and stored in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server."

So, any time you set up a new server you should take a backup of this important key. Also, every time the SMK changes you need to take a new backup as well. To take a backup you can use the BACKUP SERVICE MASTER KEY statement like this:

The statement has only two mutable parts: The path and file name of the backup file and the password that you would like the backup to be protected with.

As this file is essentially a replacement key for your encrypted data, so treat it with the same care. Pick a good password and store the file in a secure offsite location. Moreover, do not forget to store the password somewhere safe too. Without that password, the backup file is essentially worthless.

Summary

The Service Master Key is the root of the SQL Server encryption hierarchy. It is a very important asset that you need to protect. That includes taking a backup after it is created or altered. To take a backup you can use the BACKUP SERVICE MASTER KEY statement.