[mssql] TDE 암호화 적용된 DB 백업파일 복원

오늘 업무 중 아무생각없이 올해 초에 TDE 암호화를 적용한 것을 까먹고 습관처럼 운영서버 DB를 백업(backup)을 받아 개발서버에 복원(restore)을 시도하다 에러메세지가 떴다.

제목: Microsoft SQL Server Management Studio

------------------------------

Transact-SQL 문 또는 일괄 처리를 실행하는 동안 예외가 발생했습니다. 

(Microsoft.SqlServer.ConnectionInfo)

------------------------------

추가 정보:

지문이 '0xC4A17EA1163269F402BAF91553DAB2EFAA38C76F'인 서버 인증서을(를) 찾을 수 없습니다.

RESTORE FILELIST이(가) 비정상적으로 종료됩니다. 

(Microsoft SQL Server, 오류: 33111)



도움말을 보려면 다음을 클릭하십시오: 

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=33111&LinkId=20476

으잉? 이게 뭔소리여.. 하고 생각해보니 TDE 암호화...아하!

일단 복원을 하려면 TDE 암호화가 적용된 서버에 있는 인증서(Certificate)를 백업받아서 복원 대상 서버에 생성해 줘야한다.

1. TDE 암호화가 적용된 서버에 있는 Certificate 및 private key 백업

BACKUP CERTIFICATE SQLServerCertForTDE 
TO FILE = 'D:\Certi\backup\SQLServerCertForTDE.cer'
WITH PRIVATE KEY 
(
    FILE = 'D:\Certi\backup\SQLServerCertForTDE.pvk',
    ENCRYPTION BY PASSWORD = 'abcd1234asdf!@#$%^'    
);
GO

2. 마스터키 생성

USE master ;
GO

CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = '^%&UI*Utyudfasd kilh.faj.ajksl;$%^';
GO

3. 복원 대상 서버에서 위의 인증서 백업으로부터 인증서 생성

CREATE CERTIFICATE SQLServerCertForTDE 
	FROM FILE = 'D:\Certi\backup\SQLServerCertForTDE.cer'
	WITH PRIVATE KEY 
	(
		FILE = 'D:\Certi\backup\SQLServerCertForTDE.pvk'
		,DECRYPTION BY PASSWORD = 'abcd1234asdf!@#$%^'    
	);
GO

4. 복원

위의 순서대로 진행하면 이상없이 복원이 가능하다.

경로는 원본 서버에서 초기 적용값에 따라 다르며, 비밀번호의 경우 백업시 사용했던 비밀번호로 복원한다.