SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide

This is Page 2 of Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide click here to read from the beginning.

Backup Database Using Encryption Feature of SQL Server 2014

Now that you have completed the prerequisites such as Database Master Key and Backup Certificate you can go ahead and create the encrypted database backups in SQL Server 2014.

/* Execute the below TSQL code to create encrypted database backup in SQL Server 2014 */

BACKUP DATABASE [HumanResource]
TO DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupEncryptionCert
),
STATS = 5
GO

Once the above TSQL script is executed successfully the backup file will be Encrypted. Learn more about Database Compression Feature.

How to Restore an Encrypted Backup on the Same Instance

Execute the below script to restore encrypted backup of the database on the same instance of SQL Server 2014.

RESTORE DATABASE [HumanResource]
FROM DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH REPLACE, RECOVERY

How to restore Encrypted Backup on Different Instance of SQL Server were Certificate is not present

You will receive the below mentioned error message received while restoring encrypted database on the destination server when certificate is not found.

Error Message

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ''.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

In such scenarios you need to Restore Master Key and Certificate on Destination Server. Read the steps mentioned below to restore master key and certificate on another server.

How to Restore Master Key and Certificate on Destination Server

Execute the below script to create master key on the destination server.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£xP@$$Wrd';

Execute the below script to create backup certificate on the destination server.

CREATE CERTIFICATE BackupEncryptionCert
FROM FILE = 'X:\BackupCertificates\BackupEncryptionCert.bak'
WITH PRIVATE KEY (FILE = 'X:\BackupCertificates\BackupEncryptionCertKey.bak',
DECRYPTION BY PASSWORD = 'Certi%yC&mpl£xP@$$Wrd');

Once the Master Key and Certificate is created on Destination Server execute the restore database script.

RESTORE DATABASE [HumanResource]
FROM DISK = N'X:\Backups\HumanResource_Encrypted.bak'
WITH REPLACE, RECOVERY

Conclusion

As a DBA you must importantly back up the certificate or asymmetric key, preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you will not be able to restore the backup, resulting in a non-usable database backup file. Hence before implementing this feature on a Production database test it thoroughly and document all the TSQL scripts in a Source Control.

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement