Share via

  • Article

How to: Back Up a Database Master Key

The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key, and store the backup in a secure off-site location.

To back up the database master key

  1. In SQL Server Management Studio, connect to the database containing the database master key you wish to back up.

  2. Choose a password that will be used to encrypt the database master key on the backup medium. Do not use the same password that is used to encrypt the key in the database.

  3. Obtain a removable backup medium for storing a copy of the backed-up key.

  4. Identify an NTFS directory in which to create the backup of the key. This is where you will create the file specified in the next step. The directory should be protected with highly restrictive ACLs.

  5. In Query Editor, execute the following Transact-SQL command: BACKUP MASTER KEY TO FILE = '<complete path and filename>' ENCRYPTION BY PASSWORD = '<password>' ; GO 

  6. Copy the file to the backup medium and verify the copy.

  7. Store the backup in a secure, off-site location.

Security noteSecurity Note

It is usually best to create multiple copies of the backup, and to store one copy locally. The local copy can be the file you created when you backed up the database master key.