Sql-server – SQL Server take an unencrypted full backup

If you want to restore on a different server, you should be able to do so with the certificate, private key and database backup file(s).

When a certificate is created in any database in SQL Server, it is part of an encryption hierarchy. The certificate in the master database itself only contains a public key, which needs no protection, however, the master database will also contain a separate but mathematically related private key which does need to be protected. The method of protecting the private key is to encrypt it using the database master key you created in the master database prior to creating the certificate. The next layer in the encryption hierarchy is that the DMK is encrypted by the service master key. There is only one SMK on the system and it is in the master database.

Even though you don't need the DMK and SMK to restore an encrypted backup to another server, I would back up these two keys anyway as it makes recovery much more flexible.

When you restore the backup encryption certificate to the master database, what happens behind the scenes is that the private key is read from the file, decrypted using the password you provide in the restore command, then encrypted using the database master key and saved. As you know, the private key from the certificate can then be used to decrypt the Database Encryption Key in the backup file and successfully restore the database.

I don't have a specific recommendation for storing the certificate and key backup files, but they do need to be available to you and whoever does the disaster recovery in your organization.

These links are a good place to start

https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

https://msdn.microsoft.com/en-us/library/ff773063.aspx

To summarize: On EACH Sql Server Instance on which you want to enable TDE:

1). In "master', create the database master key to encrypt the certificate that will be used for TDE

USE [master];
GO 
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'StrongPassw0rd!';
GO 

2). Create the certificate we're going to use for TDE. Make sure you include the EXPIRY_DATE. The default is one year from creation date and even though TDE is technically unaffected by an expired certificate, it's still annoying to see the message 'The certificate you just installed has expired' when creating this certificate on another server.

CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Cert for Test',
EXPIRY_DATE = '99991231';;

3). Back up the certificate and its private key to a REALLY safe place. Remember the password! Losing this information will prevent you from being able to restore a TDE database,

BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'to
  );
GO

4). Switch over to the database you want to apply TDE to and create the Database Encryption Key so you'll be able to encrypt the database.

USE [DatabaseWithTDE];
GO 

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO 

5). Reconnect to master and turn on TDE for the user database

USE [master];
GO 

ALTER DATABASE [DatabaseWithTDE]
  SET ENCRYPTION ON;
GO 

6. Monitor the progress of encryption

   SELECT db_name(database_id) AS DatabaseName
   ,CASE 
       WHEN encryption_state = 0
           THEN 'No Database Encryption'
       WHEN encryption_state = 1
           THEN 'Unencrypted'
       WHEN encryption_state = 2
           THEN 'Encryption In Progress'
       WHEN encryption_state = 3
           THEN 'Encrypted'
       WHEN encryption_state = 4
           THEN 'Key Change In Progress'
       WHEN encryption_state = 5
           THEN 'Decryption In Progress'
       WHEN encryption_state = 6
           THEN 'Protection Changes In Progress'
       END AS EncryptionState
   ,percent_complete FROM sys.dm_database_encryption_keys
   

At this point, you have TDE on your chosen Sql Server instances and database.

For you to be able to restore databases from any other instance, you'll have to 'import' the certificates you created and 'exported' (Step 3 above) on all of the other instances.

So, let's say you're on SERVER1

Note that, when we originally created the certificates on each instance, they were all called TDECert. When 'importing' the certificates from the OTHER instances, you'll need to give them a unique name (FromServer2, FromServer3, etc.) - although, I'd shy away from using the actual server or instance name as part of the certificate name as that could change in the future as newer servers replace older ones and the certificates get moved around.

--Create the certificate from SERVER2.  
CREATE CERTIFICATE TDECert_FromServer2
--The directory where you saved the certificate for SERVER2
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER3.  
CREATE CERTIFICATE TDECert_FromServer3
--The directory where you saved the certificate for SERVER3
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER4.  
CREATE CERTIFICATE TDECert_FromServer4
--The directory where you saved the certificate for SERVER4
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER5.  
CREATE CERTIFICATE TDECert_FromServer5
--The directory where you saved the certificate for SERVER5
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER6.  
CREATE CERTIFICATE TDECert_FromServer6
--The directory where you saved the certificate for SERVER6
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

Go into each Sql Server instance and repeat the above 'importing' of the certificates that exist on OTHER instances. So, for SERVER2, you'd 'import' certificates from SERVER1, SERVER3, SERVER4, SERVER5 and SERVER6

At this point, you 'should' be able to restore any TDE database from the above example to any Sql Server instance in the above example.

Also, if, during a restore, you DO get the message about 'thumbprint doesn't match', you can go into EACH Sql Server instance and SELECT * FROM sys.certificates and look for the 'thumbprint' column to figure out which certificate you're missing.