-- create database
create database mydb1
-- create table mytable in database mydb1
create table mytable
id int primary key,
-- insert record to table
insert into mytable values (1,'Mohamed')
-- make a backup for database mydb1
backup database mydb1 to disk ='c:\myback.bak'
goto file myback.bak that is found in 'c:\', open it by Notepad program and try to search for a keyword 'Mohamed'. note that it's can clearly see data into backup file.
to protect data in backup, do the following steps.
-- create master key for your instanceUSE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'
-- create certificate object for your instance
CREATE CERTIFICATE mycert
WITH SUBJECT = 'My Certificate for database'
-- in specific database 'mydb1' create encryption key that associated with
-- certificate object of server that created in previous block.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert
-- open feature of encryption in database scope
ALTER DATABASE mydb1
SET ENCRYPTION ON
-- try to make backup again
backup database mydb1 to disk ='c:\myback2.bak'
goto again to 'c:\' try to open file 'myback2.bak' by Notepad program, note that all content of backup encrypted now.
now if need to restore this encrypted backup into another database in diffirence instance, the following error will be occurs.
To restore this backup without any error, wite the following code in the first server (the server which hold the main database)
BACKUP CERTIFICATE mycert
TO FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' )
The above code create backup for the exist server certificate to file named 'mydb_file.cer' and backup key of database also (private key) in file 'mydb_key.pvk'
Now go to another server (which want to restore encypted backup) and write the following code.
-- creating master key for another server instance
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd'
-- create certificate object for this server instance, this certificate
-- restored from file 'pvk', 'cer'
CREATE CERTIFICATE Inst2Cert
FROM FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk',
DECRYPTION BY PASSWORD = 'mY_P@$$w0rd');
The data retored successfuly now in the second server.
** Some Verification for SQL object
* Verify Master key on the server
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
* Verify Certifications for master key
SELECT * FROM sys.certificates
* Verify database encryption states
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted