Database Backup File Encryption in SQL Server 2008

— We can use database encryption in SQL Server 2008 to encrypt a database backup to prevent it from being install and another server, without the need to the encryption certificate and private key.

This example explains how this can be done and how to restore the database.

-- Create the server wide 'MASTER' key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd1'
go
--Now create a certificate for use with our database
CREATE CERTIFICATE adventureworks2008Cert
WITH SUBJECT = 'My DEK Certificate for adventureworks2008 database'
--set the encryption for the Northwind database by creating a database
--encryption key and password using the certificate we just created.
use adventureworks2008
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE adventureworks2008Cert
--enable encryption on the database level.
ALTER DATABASE adventureworks2008
SET ENCRYPTION ON
--check you have set encryption on (should be set to '1')
SELECT name, is_encrypted FROM sys.databases
GO
--####################################################--
--Well done the database is encrypted. Now lets test
--We can only restore this backup to a server that holds
-- a valid certificate
BACKUP DATABASE adventureworks2008
TO DISK = 'd:\mssql_dumps\adventureworks2008.bak'
WITH INIT, STATS = 10
--If you are using the same server to test this you will need to drop
-- the certificate, so export it FIRST!!
use master
BACKUP CERTIFICATE adventureworks2008Cert
TO FILE = 'd:\mssql_dumps\adventureworks2008Cert_File.cer'
WITH PRIVATE KEY (FILE = 'd:\mssql_dumps\adventureworks2008Cert_Key.pvk',
ENCRYPTION BY PASSWORD = 'pwd1234' )
--drop if on the same server
DROP CERTIFICATE adventureworks2008Cert
--Now try to restore the DB backup. It should fail
--with msg "Cannot find server certificate"
RESTORE DATABASE adventureworks2008
FROM DISK = 'd:\mssql_dumps\adventureworks2008.bak'
WITH REPLACE, STATS=10
--Import the certificate (if you are on a different SQL server,
--create a MASTER KEY first)
FROM FILE = 'd:\mssql_dumps\adventureworks2008Cert_File.cer'
WITH PRIVATE KEY (FILE = 'd:\mssql_dumps\adventureworks2008Cert_Key.pvk' ,
DECRYPTION BY PASSWORD = 'pwd1234')
--Finally, retry the database RESTORE. This time it should work
-- Congratulations!