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

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

      

      

      

    
    

      <code />
    
제목: 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. 1. TDE 암호화가 적용된 서버에 있는 Certificate 및 private key 백업

      

      

      

    
    

      <code />
    
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. 2. 마스터키 생성

      

      

      

    
    

      <code />
    
USE master ;
GO

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

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

      

      

      

    
    

      <code />
    
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. 4. 복원

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

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