RESTORE 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).
Imports a database master key from a backup file.
Applies to: SQL Server (SQL Server 2008 through current version).
When the master key is restored, SQL Server decrypts all the keys that are encrypted with the currently active master key, and then encrypts these keys with the restored master key. This resource-intensive operation should be scheduled during a period of low demand. If the current database master key is not open or cannot be opened, or if any of the keys that are encrypted by it cannot be decrypted, the restore operation fails.
Use the FORCE option only if the master key is irretrievable or if decryption fails. Information that is encrypted only by an irretrievable key will be lost.
If the master key was encrypted by the service master key, the restored master key will also be encrypted by the service master key.
If there is no master key in the current database, RESTORE MASTER KEY creates a master key. The new master key will not be automatically encrypted with the service master key.
The following example restores the database master key of the AdventureWorks2012 database.
USE AdventureWorks2012; RESTORE MASTER KEY FROM FILE = 'c:\backups\keys\AdventureWorks2012_master_key' DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04' ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'; GO