Script for Backup Encryption

Please Consider Subscribing

Subscribe

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

Please Consider Subscribing

Subscribe