Encrypted Backup On SQL Server

dbtut

6 years ago

You can encrypt your backups with SQL Server’s own encryption method in order to avoid the risk of unauthorized access to the backup.

You must first create a master key on the instance. You can create it as follows.

USE master; 
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';

You must then create a certificate. You can create it as follows.

Use Master 
GO 
CREATE CERTIFICATE MyCert 
   WITH SUBJECT = 'My Test Certification';

Use Master

CREATE CERTIFICATE MyCert

WITH SUBJECT = 'My Test Certification';

After you create your certificate, your first job is to get the backup of your certificate. You can perform this operation with the help of the following script.

BACKUP CERTIFICATE MyCert   TO FILE = 'C:\Backup\MyCert' 
    WITH PRIVATE KEY ( FILE = 'C:\Backup\MyCertPrivateKey' ,  
    ENCRYPTION BY PASSWORD = 'your_password' );

BACKUP CERTIFICATE MyCert TO FILE = 'C:\Backup\MyCert'

WITH PRIVATE KEY ( FILE = 'C:\Backup\MyCertPrivateKey' ,

ENCRYPTION BY PASSWORD = 'your_password' );

After you have backed up your certificate, you can get an encrypted backup of your database with the help of the script to your share named \\Myshare

BACKUP DATABASE [MyDB] 
TO DISK = N'\\MyShare\MyDB.BAK' 
WITH 
  COMPRESSION, 
  ENCRYPTION  
   ( 
   ALGORITHM = AES_256, 
   SERVER CERTIFICATE = MyCert  
   ), 
  STATS = 1 
GO

BACKUP DATABASE [MyDB]

TO DISK = N'\\MyShare\MyDB.BAK'

WITH

COMPRESSION,

ENCRYPTION

(

ALGORITHM = AES_256,

SERVER CERTIFICATE = MyCert

STATS = 1

To restore Encrypted Backup to another instance, you can use the article “How To Restore Encrypted Backup To Another Instance On SQL Server“.