BACKUP MASTER KEY (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Exports the database master key.
Applies to: SQL Server (SQL Server 2008 through current version).
The master key must be open and, therefore, decrypted before it is backed up. If it is encrypted with the service master key, the master key does not have to be explicitly opened. But if the master key is encrypted only with a password, it must be explicitly opened.
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.
The following example creates a backup of the AdventureWorks2012 master key. Because this master key is not encrypted by the service master key, a password must be specified when it is opened.
USE AdventureWorks2012; OPEN MASTER KEY DECRYPTION BY PASSWORD = 'sfj5300osdVdgwdfkli7'; BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey' ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg'; GO