Database Master Key
By defualt, it is protected by password.
We need to manually change the protection to SMK.
It need to create manually.
It use AES algorithm with 256 length.
Single DMK defined for each database.

Show Current DMK
show database AdventureWorks2016’s DMK information

USE AdventureWorks2016
select * from sys.symmetric_keys

Create DMK
Create DMK for database ‘AdventureWorks2016’ with password ‘P@ssw0rd’

USE AdventureWorks2016
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

Drop DMK
If this DMK is used to encrypt Asymmetric Keys & Certificates, this DMK is unable to drop.

USE AdventureWorks2016
DROP MASTER KEY

Backup DMK
Backup DMK named as DMK.dat at SQL Server’s C:\SMK\ with password ‘P@ssw0rd’

USE AdventureWorks2016
Backup MASTER KEY TO FILE = 'C:\SMK\DMK.dat' ENCRYPTION BY PASSWORD = 'P@ssw0rd'

Restore DMK
When we restore DMK, SQL Server will decrypt all key and then use the restored DMK to encrypt all key again.
If the decryption fail, the restored will fail.
We use force restore but it will cause loss of data.
DECRYPTION is the password which you set in Backup DMK.
ENCRYPTION is the password which you set in Create DMK.

-- Normal Resotre
USE AdventureWorks2012;
RESTORE MASTER KEY
FROM FILE = 'C:\SMK\DMK.dat'
DECRYPTION BY PASSWORD = 'P@ssw0rd'
ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
-- Force Restore
USE AdventureWorks2012;
RESTORE MASTER KEY
FROM FILE = 'C:\SMK\DMK.dat'
DECRYPTION BY PASSWORD = 'P@ssw0rd'
ENCRYPTION BY PASSWORD = 'P@ssw0rd'
FORCE;
GO

Using SMK to protect DMK

USE AdventureWorks2012
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Regenerate DMK
When we regenerate DMK, SQL Server will decrypt all key and then use the new DMK to encrypt all key again.
If the decryption fail, the regenerate will fail.
We use force regenerate but it will cause loss of data.

-- Normal Regenerate
USE AdventureWorks2012
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'P@ssw0rd'
-- FORCE Regenerate
USE AdventureWorks2012
ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'P@ssw0rd'

Drop existed password from DMK
If the password does not existed, it will return error ‘Cannot drop encryption by password ‘********’.’

USE AdventureWorks2012
ALTER MASTER KEY Drop ENCRYPTION BY PASSWORD = 'newP@ssw0rd'