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