Restoring encrypted database on another server (using Backup Encryption) I have two SQL Server instances on same machine. I want to create an encrypted backup on one of the databases and then restore it on the second instance. I am doing the following steps:


Create and backup database master key in the master database which is going to be used to encrypt our certificates

USE MASTER;
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';
GO

BACKUP MASTER KEY
TO FILE = 'E:\GKKeys\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';
GO

Create and back up the certificate that is going to be used for encryption:

USE MASTER;
GO

-- създаваме сертификат, който ще използвам за криптиране на backup-a
CREATE CERTIFICATE BackupEncryptTestCert
WITH SUBJECT = 'smGK_BackupCertificate'
GO

BACKUP CERTIFICATE BackupEncryptTestCert
TO FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
WITH PRIVATE KEY
(
    FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key'
   ,ENCRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
);

Creating the backup:

BACKUP DATABASE smGK
TO DISK = 'E:\GKKeys\smGKFULLEncrtypted.back'
WITH COMPRESSION, STATS = 10, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = BackupEncryptTestCert)

Now on the second instance I want to restore the master key but I am not allowed:

USE MASTER;
GO

RESTORE MASTER KEY
FROM FILE = 'E:\GKKeys\MASTER_KEY.key'
DECRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password'
ENCRYPTION BY PASSWORD = 'smGK_MasterKeyPassword';



  Msg 15317, Level 16, State 2, Line 4 The master key file does not
  exist or has invalid format.



Could anyone tell what is causing this?


Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 
    Apr 20 2015 17:29:27 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on 
    Windows NT 6.3  (Build 9600: ) (Hypervisor)
Create a brand new master key on your second instance. i.e. don't create it from backup you taken from 1st instance. Then restore certificate from the backup taken and then try. I guess you don't need master key and only certificate is required for restore purposes. Follow the below steps:  

Step1: Create Master Key

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';


Step2: Verify permissions on cert and pvt key  

Make sure SQL Server service account of second instance has FULL permissions on cert and pvt key that you created.

Step3: Create cert from backup

CREATE CERTIFICATE BackupEncryptTestCert
    FROM FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
     WITH PRIVATE KEY 
      ( 
        FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key' ,
        DECRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
      ) 


Step4: Restore the DB