Script for Backup Encryption

By | September 20, 2020
USE master;
GO
DROP DATABASE ExampleSecDB
GO
CREATE DATABASE ExampleSecDB

GO

USE ExampleSecDB
GO 
select  * into Dummy from mydummydata.dbo.dummy 
GO
/*************************************************************
CREATE YOUR MASTER KEY AND YOUR CERTIFICATE FOR TDE AND ALL OTHER TYPES
OF ENCRYPTION
USE MASTER 
GO
DROP SYMMETRIC KEY SYMMKEY
GO
DROP CERTIFICATE MYCERTIFICATE
go
DROP MASTER KEY
*************************************************************/

GO
USE MASTER 
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Thisi$notaSecurePassw0rd'

CREATE CERTIFICATE MYCERTIFICATE
WITH SUBJECT = 'ENCRPTYDATABASECERTIFICATE'

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

BACKUP MASTER KEY TO 
FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MasterKeyBackup' 
ENCRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

BACKUP CERTIFICATE MYCERTIFICATE 
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKP'
WITH PRIVATE KEY (
        FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKPK'
        ,ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
        );
CLOSE MASTER KEY
GO

BACKUP DATABASE ExampleSecDB 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\ExampleSecDB.bak' 
WITH FORMAT, INIT,  MEDIANAME = N'Newnecryptedbackup',  
NAME = N'ExampleSecDB-Full Database Backup', 
ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [MYCERTIFICATE]), COMPRESSION, STATS = 10
GO
BACKUP DATABASE [ExampleSecDB] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SecdbCompress.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'ExampleSecDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 
COMPRESSION,  STATS = 10
GO

/**********************************
use master 
DROP CERTIFICATE MYCERTIFICATE
DROP MASTER KEY
DROP DATABASE ExampleSecDB
GO
***********************************/



/**********************************
RESTORING YOUR MASTER KEY AND CERTIFICATE ON ANOTHER SYSTEM
***********************************/
RESTORE MASTER KEY
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MasterKeyBackup' 
DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd' ENCRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd' FORCE

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

CREATE CERTIFICATE MYCERTIFICATE
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKP'
WITH PRIVATE KEY (
        FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKPK'
        ,DECRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
        );
GO
DROP DATABASE ExampleSecDB
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

RESTORE DATABASE ExampleSecDB
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\ExampleSecDB.bak'
WITH MOVE 'ExampleSecDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb.mdf'
    ,MOVE 'ExampleSecDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb_log.ldf';
GO

RESTORE DATABASE ExampleSecDB
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SecdbCompress.bak'
WITH MOVE 'ExampleSecDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb.mdf'
    ,MOVE 'ExampleSecDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb_log.ldf';
GO

GO

use ExampleSecDB 
go 

select * from dummy
Category: Uncategorized

About opsadmin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.