USE master; -- Replace with your database name
SET XACT_ABORT ON 

-- Create a new master key if not already created
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'v$~2YXERm2cj:WL9dlQu|Rvh7OohY/%v:';
    PRINT 'master key created'
END

/*
-- Create a new certificate
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'Database TDE encryption',
     START_DATE = '20240101', -- Replace with the desired start date in the format 'YYYYMMDD'
     EXPIRY_DATE = '20241231' -- Replace with the desired expiry date in the format 'YYYYMMDD'

BACKUP CERTIFICATE [TDECert] TO FILE = 'd:\TDECert.cer';

BACKUP CERTIFICATE TDECert
TO FILE = 'd:\TDE_Cert.cer'
WITH PRIVATE KEY (file='d:\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='Hax0r$P@ss') 

*/

USE [AdventureWorks2022]
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert

USE master
ALTER DATABASE [AdventureWorks2022] SET ENCRYPTION ON 

SELECT [d].[name], [e].[encryption_state_desc], e.* 
FROM sys.dm_database_encryption_keys e
    JOIN sys.databases d ON d.[database_id]=e.[database_id];