Database Certificate

USE master
GO
SELECT * FROM sys.certificates;

USE TestEncryptedDB
GO
SELECT * FROM sys.certificates;

Note:

• Certificate is different for different databases.
• USE master database for TDE certificate.
• If expiration date not specified, default to 1 year.

Note that there are several certificates listed, including one that looks like it’s for authentication (##MS_SQLAuthenticatorCertificate##). This is where SQL Server stores its certificates, in the master database. Obviously, if you create an additional certificate in the master database (such as for use with Transparent Data Encryption), you’ll see them as well.

Create Certificate

USE master;  
GO  

-- 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  

USE master;  
GO  

CREATE CERTIFICATE Mycertificate01 WITH SUBJECT = 'Mycertificate01';  
GO  

The ENCRYPTION BY PASSWORD option is not required when the private key is encrypted with the database master key. Use this option only when the private key is encrypted with a password. If no password is specified, the private key of the certificate will be encrypted using the database master key. If the master key of the database cannot be opened, omitting this clause causes an error.

You do not have to specify a decryption password when the private key is encrypted with the database master key.

Backup Certificate

USE master
GO

BACKUP CERTIFICATE Mycertificate01 TO FILE = 'D:\TDE\ExportedCertificate_Mycertificate01';  
GO  

Backup the certificate with the master key from master database.

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  

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15