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