Enable TDE on SQL Server

Create Database Master Key and Certificate

Note:

• USE master database.
• You should backup the Certificate (With Master Key) as soon as you create the certificate.

USE master;  
GO  

/*
DROP CERTIFICATE Mycertificate01
DROP MASTER KEY   
*/

-- Creates the database master key. (This is not the service master key)
-- The key is encrypted using the password "MyPassword01". 
CREATE MASTER KEY 
	ENCRYPTION BY PASSWORD = 'MyPassword01';  
GO  

CREATE CERTIFICATE Mycertificate01 
	WITH SUBJECT = 'Mycertificate01';  
GO  

USE master;  
GO  

BACKUP CERTIFICATE Mycertificate01   
TO FILE = 'D:\TDE\Mycertificate01_backup'  
WITH PRIVATE KEY   
(  
    FILE = 'D:\TDE\MyMasterKey_backup',  
    ENCRYPTION BY PASSWORD = 'MyPassword01'  
);  
GO  

Note:

• You can not create a Master Key if a Master Key is already created.
• You can not drop a Master Key if there are existing Certificate created with the Master Key.

Enable Encryption on Specific Database

Note: Go to the specific database. For example: USE TestEncryptedDB.

USE TestEncryptedDB;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER CERTIFICATE Mycertificate01;  
GO  
-- Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

ALTER DATABASE TestEncryptedDB 
SET ENCRYPTION ON;  
GO  

Note:

• If you get the error below while trying to encrypted the database, run a log backup. Error: This command requires a database encryption scan on database ‘TestEncryptedDB’. However, the database has changes from previous encryption scans that are pending log backup. Take a log backup and retry the command.
• Enabling encryption on a large database will take some time.

Database Encryption Check Query

-- Same for master or specific database.
SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

-- Different for master and specific database.
SELECT * FROM sys.dm_database_encryption_keys  
WHERE encryption_state = 3;  
GO 

-- Different for master and specific database.
SELECT * FROM sys.certificates;

Note:

Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available. For more information, see SQL Server Certificates and Asymmetric Keys.

Note: You must USE master database, else you will get the following error: Cannot find the certificate ‘Mycertificate01’, because it does not exist or you do not have permission.

Disabling Encryption on Specific Database

USE master
go

ALTER DATABASE TestEncryptedDB SET ENCRYPTION OFF

USE TestEncryptedDB
GO
DROP DATABASE ENCRYPTION KEY   

Another Example

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

USE [master];
GO 

-- Create the database master key
-- to encrypt the certificate
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!';
GO 

-- Create the certificate we're going to use for TDE
CREATE CERTIFICATE TDECert
  WITH SUBJECT = 'TDE Cert for Test';
GO 

-- Back up the certificate and its private key
-- Remember the password!
BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

-- Create our test database
CREATE DATABASE [RecoveryWithTDE];
GO 

-- Create the DEK so we can turn on encryption
USE [RecoveryWithTDE];
GO 

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

-- Exit out of the database. If we have an active 
-- connection, encryption won't complete.
USE [master];
GO 

-- Turn on TDE
ALTER DATABASE [RecoveryWithTDE]
  SET ENCRYPTION ON;
GO 

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-ver15