Note: This is for Service Master Key (SMK). This is not the same as Database Master Key (DMK).

The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly secures all other keys in the tree. If a dependent key cannot be decrypted during a forced restore, data that is secured by that key will be lost.

It is unlikely that you will ever need to restore the service master key. If you do, you should proceed with extreme caution.

Backup of Service Master Key

-- Creates a backup of the service master key.
USE master;
GO
BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp_backups\keys\service_master_ key'
    ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';
GO

Restore of Service Master Key

-- Restores the service master key from a backup file.  
RESTORE SERVICE MASTER KEY   
    FROM FILE = 'c:\temp_backups\keys\service_master_key'   
    DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';  
GO

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/restore-the-service-master-key?view=sql-server-ver15

Last modified: April 20, 2020

Author

Comments

Write a Reply or Comment