Backup and restore of encryption keys and restoring

By | November 19, 2019

 

USE
master;

/*************************************************************

CREATE YOUR MASTER KEY AND YOUR CERTIFICATE FOR TDE AND ALL OTHER TYPES

OF ENCRYPTION

*************************************************************/

OPEN
MASTER
KEY
DECRYPTION
BY
PASSWORD
=
‘Isthis@securePa$$word?’;

BACKUP
MASTER
KEY
TO
FILE
=
‘c:\work\exportedmasterkey’


ENCRYPTION
BY
PASSWORD
=
‘abcd@1234’;

GO

BACKUP
CERTIFICATE MyServerCert TO
FILE
=
‘c:\work\MyServerCert’


WITH
PRIVATE
KEY (
FILE
=
‘c:\work\MyServerCertKey’
,


ENCRYPTION
BY
PASSWORD
=
‘997jkhUbhk$w4ez0876hKHJH5gh’
);

GO

/**********************************

DROP CERTIFICATE MyServerCert

DROP MASTER KEY

GO

***********************************/

 

/**********************************

RESTORING YOUR MASTER KEY AND CERTIFICATE ON ANOTHER SYSTEM

***********************************/

 

RESTORE
MASTER
KEY
FROM
FILE
=
‘c:\work\exportedmasterkey’


DECRYPTION
BY
PASSWORD
=
‘abcd@1234’


ENCRYPTION
BY
PASSWORD
=
‘Isthis@securePa$$word?’


FORCE

 


OPEN
MASTER
KEY
DECRYPTION
BY
PASSWORD
=
‘Isthis@securePa$$word?’;

 

    CREATE
CERTIFICATE MyServerCert


FROM
FILE
=
‘c:\work\MyServerCert’


WITH
PRIVATE
KEY (FILE
=
‘c:\work\MyServerCertKey’,


DECRYPTION
BY
PASSWORD
=
‘997jkhUbhk$w4ez0876hKHJH5gh’);

GO

OPEN
MASTER
KEY
DECRYPTION
BY
PASSWORD
=
‘Isthis@securePa$$word?’;

USE [master]

RESTORE
DATABASE [AirlinePerformanceTuningDB]

FROM
DISK
=
N’G:\Program Files\SQL Server\SQL2019B\MSSQL15.SQL2019B\MSSQL\Backup\TUNINGTDE.BAK’


WITH
MOVE
‘AirlinePerformanceTuningDB’
TO
‘G:\Program Files\SQL Server\SQL2019B\MSSQL15.SQL2019B\MSSQL\DATA\tde.mdf’,


MOVE
‘AirlinePerformanceTuningDB_log’
TO
‘G:\Program Files\SQL Server\SQL2019B\MSSQL15.SQL2019B\MSSQL\DATA\tde.ldf’;

GO

 

GO


 

Category: Databases

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.