[SQL Server] DB 복제하기

IT 관련해서 첫 포스팅이네요.

오늘 포스팅 할 내용은 DBA를 하다보면 가장 자주하게 되는 작업 중 하나인 DB Migration 입니다.

(제가 하는 업무 위주로 포스팅 할 계획이니 필요하신 분께 참고가 됐으면 좋겠네요 ^^)

Oracle DB부터 할까 하다가 요새 이쪽에 대해 저도 공부하는 입장에서 MS사의 SQL Server부터 정리하겠습니다.

일단 왜 DBA가 왜 이런 업무를 자주하게 되는지부터 간략히 말씀드리자면

DBA의 가장 큰 Role은 운영입니다.

그리고 이 운영 업무를 잘 하기 위해서는 개발자들에게 확실한 테스트를 거칠 수 있도록 Support하는게 중요한데요.

그 Test 환경을 구축해 주기 위해 DB 복제가 필요합니다.

테스트 환경을 한 번 만들어 주면 끝나는게 아니라 운영 중에도 고객의 요구사항에 의해

추가적인 개발과 Update가 들어가게 되는데 상황에 따라 운영 서버의 최신 Data를 요구합니다.

그렇기에 개발자의 요청에 따라 최신 운영의 data를 개발에 부어주거나 (복제) 새로 생성해 주게 됩니다.

반대의 경우 처음 개발 프로젝트를 마치고 개발(Test)에서 운영(Live)으로 Go-Live 할 때에도 

개발의 Data를 부어주게 됩니다.

이러한 상황은 간혹 발생하는데 관리해야 할 시스템이 몇 백개가 넘어가면 아무래도 이러한 일이 잦게 되죠.

자 그럼 서론은 여기까지로 하고 DB를 복사하는 방법에 대해 알아보죠.

방법에는 여러 가지가 있을 수 있는데...

제가 자주 쓰는 방법 위주로 설명드릴께요.

가장 Simple한 방법은 SQL Server의 Data와 Log 파일인 mdf, ldf 파일을 원하는 DB로 복사해 주면됩니다.

여기서 주의해야 할 점은 해당 DB를 잠시 Offline 상태로 바꿔줘야 한다는 점 인데요.

Online 상태의 DB는 아무도 사용하지 않는다고 해도 DB 내부적으로 여러가지 작업을 하고 있기 때문에

파일을 쓰거나 복사 할 수가 없습니다.

윈도우 상의 해당 파일을 해당 서버로 옮긴 후 대상 DB에서 sp_attach_db 를 이용해 파일을 추가해주면 완료.

   EXEC sp_attach_db @dbname = N'pubs',  
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'   

하지만 서비스를 잠시 내려야 한다는 점은 관리자에겐 상당한 부담이 될 수 있는데요.

이 방법은 24시간 Live가 아니어도 되는 DB의 경우에만 사용해 주세요.

아래는 그냥 명령어에 대한 설명이니 참고만 하세요.

sp_attach_db

서버에 데이터베이스를 첨부합니다.

구문

sp_attach_db [ @dbname = ] 'dbname' ,
     [ @filename1 = ] 'filename_n' [ ,...16 ]

인수

[@dbname =] 'dbname'

서버에 첨부될 데이터베이스의 이름입니다. 이 이름은 고유해야 합니다. dbname은 sysname이며 기본값은 NULL입니다.

[@filename1 =] 'filename_n'

데이터베이스 파일 경로를 포함한 물리적 이름입니다. filename_n은 nvarchar(260)이며 기본값은 NULL입니다. 16개까지 파일 이름을 지정할 수 있습니다. 매개 변수 이름은 @filename1에서 시작하여 @filename16까지 증가합니다. 파일 이름 목록은 데이터베이스의 다른 파일을 지정하는 시스템 테이블이 들어 있는 주 파일을 적어도 하나 이상 포함해야 합니다. 또한 목록은 데이터베이스가 분리된 다음 이동된 모든 파일을 포함해야 합니다.

반환 코드 값

0(성공) 또는 1(실패)

결과 집합

없음

비고

 명시적 sp_detach_db 작업을 사용하여 서버에서 이전에 분리된 데이터베이스만이 sp_attach_db를 사용할 수 있습니다.17개 이상의 파일을 지정해야 하는 경우에는 FOR ATTACH 절이 있는 CREATE DATABASE를 사용하십시오.

데이터베이스가 분리된 서버 외의 서버에 데이터베이스를 첨부한 경우 및 분리한 데이터베이스를 복제할 수 있는 경우, sp_removedbreplication을 실행하여 데이터베이스에서 복제본을 제거해야 합니다.

사용 권한

sysadmin 고정 서버 역할의 구성원만이 이 프로시저를 실행할 수 있습니다

두번째로는 Live DB에서 주로 사용하는 방법인데요.

중요한 DB일 수록 자주 Full Backup과 Transaction Log Backup 등으로 백업을 받습니다. (회사 정책에 따라 달라요)

이를 이용해서 간단하게 복사가 가능합니다.

먼저 해당 DB의 백업 파일(주로 *.bak)을 대상 DB로 복사합니다.

그리고 Restore(복원)하는 원리로 DB를 생성하게 됩니다.

일단 백업 받은 파일(*.bak)의 내용을 확인합니다.

--백업세트에포함된파일목록확인

RESTORE FILELISTONLY

FROMDISK = 'd:\Backup\MESDB.bak'

 

해당 쿼리를 돌리면 실제 백업받은 DB의 mdf와 ldf 명 등을 확인 할 수 있습니다.

이를 바탕으로 아래 처럼 복원을 시작합니다. 

--풀백업복원

RESTOREDATABASE MESDB

FROMDISK = 'd:\Backup\MESDB.bak'

WITH

NORECOVERY,

MOVE'MESDB' TO 'd:\test\MESDB.mdf',

MOVE'MESDB_log' TO 'd:\test\MESDB_log.ldf',

stats= 1

GO

  --로그백업복원

RESTORELOG MESDB

FROMDISK = 'd:\Backup\MESDB_log.trn'

 

참고로 NORECOVERY 옵션을 사용하면 추가로 트랜잭션 로그 백업 받은 내용도 이어서 복원이 가능합니다.

bak 파일 복원 후에도 아직 Recovery 상태가 아니다라고 표시해 주는 옵션입니다.

몇 시간 단위까지 복원 할 이유가 없다면 NORECOVERY 옵션을 제거하면 바로 DB 복제(복원)를 완료 할 수 있습니다.

여기까지 마치면 DB 목록에서 아래와 같이 새로 생성된 DB를 확인 할 수 있습니다. :)

오늘은 여기까지 정리하고 다음에 기회가 되면 DB 백업에 대해서 정리해 보도록 할꼐요.

그럼 편안한 밤 되세요.