SQL Server cell level encryption. Backup/Restore scenario. Lost DMK password scenario

----------------------------Steps to achieve cell level encryption---------------------------------------

--CREATE MASTER KEY ENCRYPTION BY
--PASSWORD = 'myPass'
--GO

--CREATE CERTIFICATE EncDemoCert
--   WITH SUBJECT = 'Enc Demo SSN Encryption';
--GO

--CREATE SYMMETRIC KEY EncDemoSSNKey
--    WITH ALGORITHM = DES
--    ENCRYPTION BY CERTIFICATE EncDemoCert;
--GO

--Alter table Customer add EncSSN varbinary(500) null


--OPEN SYMMETRIC KEY EncDemoSSNKey
--DECRYPTION BY CERTIFICATE EncDemoCert;
--UPDATE Customer
--SET [EncSSN] = EncryptByKey(Key_GUID('EncDemoSSNKey'), convert(varchar,SSN))
--CLOSE SYMMETRIC KEY EncDemoSSNKey

                           
select * from Customer


--OPEN SYMMETRIC KEY EncDemoSSNKey
--DECRYPTION BY CERTIFICATE EncDemoCert;
--select *
--, CONVERT(VARCHAR, DecryptByKey(EncSSN)) as test
--from Customer
--CLOSE SYMMETRIC KEY EncDemoSSNKey

--------------------------------------Backup Restore scenario---------------------------------------

Lets say the database where encryption is done needs to backed-up and restored. In a full-backup scenario, the DMK and symmetric keys are also automatically backed-up and restored in the target server.

However to use it the DMK has be regenerated using the following queries - 

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'myPass'; 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'myPass_New';

CLOSE MASTER KEY; 

----------Backup Restore scenario - Source DB server DMK password lost-----------------

If the source server DMK password (myPass in this case) is lost, then a new password can be added to its database master key using the following query in the SOURCE server- 

ALTER MASTER KEY 

    ADD ENCRYPTION BY PASSWORD = 'myPass_New2'.

After the above query, take the backup of the database, restore it in the target server use the queries mentioned above in the "Backup Restore scenario" 

-----------------------------------------Side Note------------------------------------------------------

You have some data in SQL Server encrypted with cert > key > DMK. (all at DB level, not server level)
If you run the following 2 commands, SQL Server will not allow -
--DROP CERTIFICATE EncDemoCert
--DROP MASTER KEY

However if you run the following query
--DROP SYMMETRIC KEY EncDemoKey
the symm key will be deleted permanently and all the encrypted data can NEVER be recovered.

So it is always advisable to backup the certificate and keys to a physical file.

(https://msdn.microsoft.com/en-IN/library/ms178578.aspx)