Edit

Restore a service master key

Applies to: SQL Server

This article describes how to restore the service master key in SQL Server by using Transact-SQL.

Warning

It's unlikely that you ever need to restore the service master key. If you do, proceed with extreme caution. For more information, see Back up a service master key.

Remarks

When you restore the service master key, SQL Server decrypts all the keys and secrets encrypted with the current service master key, and then encrypts them with the service master key loaded from the backup file.

If any one of the decryptions fails, the restore fails. You can use the FORCE option to ignore errors, but this option causes the loss of any data that can't be decrypted.

Regenerating the encryption hierarchy is a resource-intensive operation. Schedule this operation during a period of low demand.

Caution

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 can't be decrypted during a forced restore, data secured by that key is lost.

Permissions

Requires CONTROL SERVER permission on the server.

Restore the service master key

  1. Retrieve a copy of the backed-up service master key, either from a physical backup medium or a directory on the local file system.

  2. Connect to the SQL Server instance where you want to restore the service master key. You can connect to an instance of SQL Server using any familiar SQL Server client tool, such as sqlcmd, SQL Server Management Studio (SSMS), or the MSSQL extension for Visual Studio Code.

  3. Review and run the following Transact-SQL script.

    In this example, you restore the service master key from C:\Backups\Keys\service_master_key. Change these settings to match your environment.

    USE master;
GO

RESTORE SERVICE MASTER KEY
    FROM FILE = 'C:\Backups\Keys\service_master_key'
    DECRYPTION BY PASSWORD = '<password>';
GO

Related content

  • Last updated on