Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server
This article describes how to back up the Service Master key in SQL Server by using Transact-SQL. The service master key is the root of the encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. It 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.
We recommend that you back up the master key as soon as it is created, and store the backup in a secure, off-site location.
Requires CONTROL permission on the database.
In SQL Server Management Studio, connect to the SQL Server instance containing the service master key you wish to back up.
Choose a password that will be used to encrypt the service master key on the backup medium. This password is subject to complexity checks. For more information, see Password Policy.
Obtain a removable backup medium for storing a copy of the backed-up key.
Identify an NTFS directory in which to create the backup of the key. This directory is where you will create the file specified in the next step. The directory should be protected with highly restrictive access control lists (ACLs).
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
-- 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
Note
The file path to the key and the key's password (if it exists) will be different than what is indicated above. Make sure that both are specific to your server and key set-up.
Copy the file to the backup medium and verify the copy.
Store the backup in a secure, off-site location.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayTraining
Module
Back up and restore your Azure SQL database - Training
Learn how to protect the data in your Azure SQL database and recover from data loss or corruption with backup and restore.
Documentation
Restore a database master key - SQL Server
Learn how to restore the database master key in SQL Server by using SQL Server Management Studio with Transact-SQL.
Back up a database master key - SQL Server
Learn how to back up a database master key in SQL Server by using Transact-SQL. This essential key encrypts other keys and certificates.
ALTER SERVICE MASTER KEY (Transact-SQL) - SQL Server
ALTER SERVICE MASTER KEY (Transact-SQL)