USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecretP@ssw0rd'
GO
BACKUP MASTER KEY TO FILE = 'C:\temp\MasterKey.pkf' 
    ENCRYPTION BY PASSWORD = 'SecretP@ssw0rd'
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'TDE Certificate'
GO
BACKUP CERTIFICATE TDECertificate TO FILE = 'C:\temp\TDECertificate.cer'
    WITH PRIVATE KEY 
      ( 
        FILE = 'C:\temp\TDECertificate.pvk' ,
        ENCRYPTION BY PASSWORD = 'SecretP@ssw0rd' 
      ) 
GO
CREATE DATABASE TDEDatabase
GO
USE TDEDatabase
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER
CERTIFICATE TDECertificate
GO
ALTER DATABASE TDEDatabase SET ENCRYPTION ON
GO
-- We have Encription ON
-- Verify Encription
WAITFOR DELAY '00:00:30'
SELECT *
FROM sys.dm_database_encryption_keys
GO
BACKUP DATABASE [TDEDatabase] TO DISK = N'c:\temp\TDEDatabase.bak' WITH
INIT, STATS = 10
GO
ALTER DATABASE TDEDatabase SET ENCRYPTION OFF
GO
DROP CERTIFICATE TDECertificate 
GO
BACKUP DATABASE [TDEDatabase] TO DISK = N'c:\temp\TDE-OFFDatabase.bak' WITH
INIT, STATS = 10
GO
DROP DATABASE [TDEDatabase] 
GO
RESTORE DATABASE [TDEDatabase] FROM  DISK = N'c:\temp\TDE-OFFDatabase.bak' 
GO
/*
Processed 176 pages for database 'TDEDatabase', file 'TDEDatabase' on file 1.
Processed 1 pages for database 'TDEDatabase', file 'TDEDatabase_log' on file 1.
Msg 3283, Level 16, State 1, Line 1
The file "TDEDatabase_log" failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*/