How to: Back Up a Database Master Key

New: 17 July 2006

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 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.

See Also

Tasks

How to: Create a Database Master Key

Other Resources

BACKUP MASTER KEY (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance