How to backup, restore and regenerate the Service Master Key in SQL Server

In an SQL Server instance there can be only one Service Master Key (SMK), that is generated automatically the first time it is needed to encrypt another key. It is the root of all encryption operations, and it is very important to back it up and store the backup in a secure place, if there is a need to restore it for any reason.

You can backup the Service Master Key using T-SQL code. You need to specify the location of the backup file and choose a password used to encrypt the Service Master Key in the backup file. You will need to supply this password to restore the Service Master Key.

To backup the Service Master Key use this code:

USE master;
BACKUP SERVICE MASTER KEY TO FILE = 'D:\Backup\SMK.bak'
ENCRYPTION BY PASSWORD = 'Password';
GO

The password used to encrypt the Service Master Key in the backup file is subject to complexity checks. If the password does not meet Windows policy requirements, you will receive the following error:

Msg 15118, Level 16, State 1, Line 2
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

To restore the Service Master Key from the backup file use this code:

USE master;
RESTORE SERVICE MASTER KEY FROM FILE = 'D:\Backup\SMK.bak'
DECRYPTION BY PASSWORD = 'Password';
GO

When you restore the Service Master Key, all the keys that were encrypted with the current Service Master Key are decrypted first, and then encrypted with the restored key.

If you try to restore the key that is the same as the current key, you will receive the following message:

The old and new master keys are identical. No data re-encryption is required.

You can regenerate the Service Master Key using this code:

USE master;
ALTER SERVICE MASTER KEY REGENERATE;
GO

The REGENERATE statement will generate a new SMK. Don't forget to backup the new key. When you regenerate the Service Master Key, all the keys that were encrypted with the current key are decrypted first, and then encrypted with the new key, so it can be a resource-intensive operation.