백업 전략 세우기

전체 데이터베이스 백업은 항상 수행되어야 합니다. 일반적으로 트랜잭션 로그 백업은 대부분의 경우 수행합니다. 트랜잭션 로그 백업을 수행하지 않는 예외적인 경우는 데이터의 변경이 드물게 발생하거나 테스트 환경에서입니다. 차등 백업은 많은 트랜잭션이 발생하고 로그 백업의 크기가 큰 환경에서 주로 사용됩니다. 파일과 파일 그룹 백업 전략은 대용량 데이터베이스 환경에서 사용합니다. 다중 파일로 구성된 데이터베이스라도 한 번에 하나의 파일로 백업할 수 있습니다. 백업에 관한 정보는 엔터프라이즈 관리자를 사용하거나 쿼리 분석기에서 RESTORE 명령어를 수행하여 시스템 테이블을 쿼리하여 확인할 수 있습니다.

수칙1. 시스템 데이터베이스는 변경이 발생할 때마다 백업해야 합니다.

사용자 데이터베이스뿐만 아니라, 시스템 데이터베이스에도 시스템에 관련된 중요한 정보들이 있으므로, 백업을 합니다. Master 데이터베이스와 msdb 데이터베이스는 데이터베이스에 변경이 발생할 때마다 백업하는 것이 원칙입니다. 데이터베이스의 생성 및 변경, 로그인 정보의 변경, 연결된 서버의 변경, 구성 변경 등의 작업이 수행되면 master 데이터베이스 백업을 수행해야 합니다. 작업, 경고, 작업자, 스케쥴 등이 생성되거나 변경될 때에는 msdb를 백업해야 합니다.
- Master, msdb : 단순 복구 모델의 전체 백업
- Model : 전체 복구 모델의 전체 백업

수칙2. 백업 전략은 복구 시간까지 감안하여 계획을 세웁니다.

백업전략은 데이터의 중요성, 데이터의 변경 주기, 복구 시간 등 여러 가지 요인들을 고려하여 수립합니다.

수칙3. 트랜잭션 로그를 정기적으로 백업하지 않는다면, 정기적으로 비워 주어야 합니다.

트랜잭션 로그가 가득 차면, 데이터베이스에서의 모든 변경 작업은 트랜잭션 로그가 삭제되거나 로그가 확장될 때까지 중단되므로, 로그 파일은 자동으로 증가되도록 설정할 것을 권고합니다. 그리고, 사용된 로그 공간의 양은 지속적으로 스크립트나 감사 테이블 또는 SQL Server:Databases 객체의 카운터 Percent Log Used의 성능 상태 경고를 통하여 모니터링해야 합니다.
어떤 시스템의 경우에는 트랜잭션 로그 파일의 크기가 데이터 파일의 수십배에 달하는 경우를 간혹 볼 수 있습니다. 그 이유는 데이터베이스의 복구 모델이 전체(FULL) 또는 대량 로그(BULK_LOGGED)인데, 데이터베이스 전체 백업만 수행하고 로그 백업이나 삭제 작업은 수행하지 않았기 때문입니다. 전체 백업을 수행하더라도 트랜잭션 로그는 삭제되지 않으므로 주기적인 트랜잭션 로그 백업 또는 트랜잭션 로그 삭제가 필요합니다. 중요한 데이터가 저장된 데이터베이스라면 트랜잭션 로그를 정기적으로 백업하는 것을 권고하며, 테스트 DB와 같이 트랜잭션 로그 백업이 필요하지 않는 경우라면 트랜잭션 로그를 정기적으로 삭제해 주어야 합니다.

[예제] 트랜잭션이 완료된 로그 삭제하기

[참고] 데이터베이스가 단순 복구 모델이거나 "truncate log on checkpoint" 옵션이 선택되어 있을 때 트랜잭션 로그 백업을 하면, 엔터프라이즈 관리자에서는 트랜잭션 로그 옵션이 비활성화 상태가 되고, 쿼리 분석기에서는 4208 오류가 반환됩니다. 트랜잭션 로그 백업을 수행하기 위해서는, "truncate log on checkpoint" 옵션이 비활성화 상태라야 합니다.

수칙4. 백업 파일은 데이터베이스 파일이 저장된 디스크와 물리적으로 다른 디스크에 저장합니다.

디스크로 백업하고 별도의 위치로 백업 파일을 저장하는 것이 원칙입니다만, 여건상 하드 디스크에만 백업받는 경우에는 최소한 데이터베이스 파일이 저장된 디스크와 물리적으로 다른 디스크로 백업합니다.

수칙5. 주기적으로 백업 파일의 유효성과 백업이 실제로 정상적으로 복원되는지 테스트합니다.

"백업 검증하기"에 있는 내용을 참조하여 백업 세트의 유효성을 점검할 것을 권고합니다. 만일의 경우를 대비하여 백업을 열심히 받아 두었는데 막상 문제가 발생해서 복원하려고 하면 복원이 정상적으로 되지 않아서 낭패를 겪는 고객사를 간혹 볼 수 있습니다. 백업 장비에 문제가 있는 경우도 있으므로, 특히 새로운 백업 장비 도입 시에는 백업 후 반드시 다른 DB 서버에서 복원을 테스트하기 바랍니다.

백업 성능 향상시키기

데이터베이스 파일이 여러 개의 디스크에 분산되어 있으면 병렬로 디스크 I/O를 처리할 수 있으므로 백업 성능에 도움이 됩니다. 그리고 다중의 백업 디바이스로 백업하면 백업 수행 속도가 향상됩니다. 스트라이핑된 백업 세트를 생성할 때에는, 모든 백업 디바이스의 미디어 타입이 동일해야 합니다. 디스크 드라이브가 테이프보다 훨씬 빠르며 테이프 백업은 SQL Server에 물리적으로 장착이 되어야만 가능합니다. 속도를 향상시키고자 한다면, 먼저 직접 디스크에 백업을 받은 다음에 백업 파일을 오프사이트로 저장하기 위해 써드 파티 도구를 사용하여 테이프로 복사하거나 다른 드라이브로 복사합니다.

[참고] 네트워크 드라이브 백업이 가능하지만, 백업성능이 좋지 않으며 네트워크 부하를 가중시킬 수 있으므로 유의하기 바랍니다.

백업 검증하기

RESTORE VERIFYONLY를 사용하면 백업을 복원하지 않고 백업 디바이스를 검사하여 백업 세트가 올바른지 그리고 모든 볼륨을 제대로 읽을 수 있는지 확인할 수 있습니다. 그러나, 이 명령어는 DB 데이터의 손상 여부까지 확인해 줄 수는 없습니다. 그러므로 대기 서버를 사용하여 DBCC 명령어를 수행하여 데이터의 손상 여부를 확인해야 완벽한 점검이 가능합니다. 주기적으로 운영 서버가 아닌 대기 서버에서 DB를 복원하고 DBCC CHECKDB 명령어를 사용하여 백업에 포함된 데이터가 손상되지 않았는지를 확인할 것을 권고합니다.

복원 전략 세우기

손상된 데이터베이스를 복구하는 첫번째 단계는 현재의 트랜잭션 로그를 백업하는 것입니다. 이 작업은 트랜잭션 로그 파일이 액세스 가능하고 손상되지 않았을 때 가능합니다. 비록 데이터베이스가 suspect 상태일지라도, 마지막 트랜잭션 로그 백업의 시점부터 데이터베이스 파일이 손상되었을 시점까지의 전체 트랜잭션 로그를 백업합니다.
복구 과정에서 복구되는 마지막 백업은 문제 발생 후 백업한 트랜잭션 로그 백업이거나 마지막 로그 백업이며, 사용 가능한 트랜잭션 로그 백업이어야 합니다. 마지막 백업 이전의 복원 단계에서는 NORECOVERY 옵션을 사용해야 하며, 마지막 백업의 복구 시에는 RECOVERY 옵션을 사용합니다.

[참고] 트랜잭션 로그 백업이 RECOVERY 옵션으로 복구되면, 추가적인 로그는 복구될 수 없습니다. 만일 추가적인 로그가 존재하면, 복구 프로세스는 반드시 마지막 전체 데이터베이스 백업을 가지고 처음부터 다시 시작해야 합니다.

• 전체 백업을 다른 서버에 복원하기

  백업 일시	백업
  월 05:00	BACKUP DATABASE Sample   TO DISK='F:\DBBackup\sample.bak' WITH NOINIT
  화 05:00	BACKUP DATABASE Sample   TO DISK= 'F:\DBBackup\sample.bak' WITH NOINIT
  수 05:00	BACKUP DATABASE Sample   TO DISK= 'F:\DBBackup\sample.bak' WITH NOINIT

  [따라하기]
  전체 백업을 새로운 서버에 복원한 후, 새로운 서버의 로그인 정보를 복원한 데이터베이스의 사용자와 링크합니다. 사용자에 대한 로그인이 변경되는 경우에는 sp_change_users_login을 사용하면, 사용자의 권한을 상실하지 않고 새 로그인에 사용자를 링크할 수 있습니다.

  1. 백업 파일에 대한 정보를 확인합니다. <PRE>-- 모든 백업 세트들에 대한 백업 헤더 정보를 검색합니다. RESTORE HEADERONLYFROM DISK='F:\DBBackup\sample.bak'GO-- 복원할 백업 세트에 포함된 데이터베이스와 로그 파일 정보를 확인합니다.RESTORE FILELISTONLYFROM DISK='F:\DBBackup\sample.bak'WITH FILE = 3GO</PRE>2. 원하는 전체 백업 파일을 새로운 서버에 복원 합니다. <PRE>USE masterGORESTORE DATABASE SampleFROM DISK='F:\DBBackup\sample.bak'WITH FILE = 3, RECOVERYGO</PRE>만약 복원에 문제가 발생하면, DBCC VERIFYONLY 명령어를 사용하여 백업 세트의 유효성을 확인합니다. 이 명령어는 실제 복원 작업보다는 수행 시간이 조금 짧기는 하지만, 수행 시간이 오래 걸립니다. <PRE>RESTORE VERIFYONLYFROM DISK='F:\DBBackup\sample.bak'WITH FILE = 3GO</PRE>3. 복원이 완료되면, 사용자 정보를 연결합니다. <PRE>USE SampleGOEXEC sp_change_users_login 'Update_One', 'dbadmin', 'dbadmin'GO</PRE>[참고] SQL Server는 GUID를 생성하여 syslogins.sid에 저장하며 이 sid를 로그인 이름의 security_identifier로 사용합니다. 서버가 다르면 Login 계정이 동일하더라도 이 sid값은 달라지며 로그인과 사용자에 대한 처리는 sid를 사용하므로, 원격 서버로 데이터베이스를 복원한 경우에는 새로운 서버의 로그인 계정과 복원한 데이터베이스의 사용자를 연결하는 작업이 필요합니다. <PRE>SELECT SUSER_SNAME (security_identifier)SELECT sid FROM master..syslogins WHERE name='dbadmin'SELECT sid FROM Sample..sysusers WHERE name='dbadmin'</PRE>
• 전체 백업과 차등 백업을 실행한 경우의 복원하기

  백업 일시	백업
  월 05:00	BACKUP DATABASE Sample TO DISK='F:\DBBackup\sample.bak' WITH INIT
  화 05:00	BACKUP DATABASE Sample TO DISK='F:\DBBackup\sample.bak' WITH DIFFERENTIAL, NOINIT
  수 05:00	BACKUP DATABASE Sample TO DISK='F:\DBBackup\sample.bak' WITH DIFFERENTIAL, NOINIT

  [따라하기] 차등 백업을 사용하여 복원하기
  차등 백업은 마지막 데이터베이스 백업 이후에 수정된 모든 페이지의 복사본을 저장하므로, 전체 백업 이후의 최종 차등 백업만 복원하면 됩니다. 문제가 발생하여 복원하는 경우에는 항상 복원 전에 현재의 트랜잭션 로그를 백업받습니다. (로그 백업이 가능한 경우)

  1. 백업 세트에 대한 정보를 확인합니다. (전체 백업을 다른 서버에 복원하기 참조)

  2. 장애가 발생하기 전의 마지막 전체 백업을 복원합니다.

  <PRE>USE masterGORESTORE DATABASE sampleFROM DISK='F:\DBBackup\sample.bak'WITH FILE = 1, NORECOVERYGO</PRE>

  3. 복원한 전체 백업 후의, 마지막 차등 백업 파일을 복원합니다.

  <PRE>RESTORE DATABASE sampleFROM DISK='F:\DBBackup\sample.bak'WITH FILE = 3, RECOVERYGO</PRE>
• 전체 백업과 트랜잭션 로그 백업을 실행한 경우의 복원하기

  백업 일시	백업
  월 05:00	BACKUP DATABASE Sample TO DISK='F:\DBBackup\sample.bak' WITH INIT
  월 10:00	BACKUP LOG Sample TO DISK='F:\DBBackup\sample_log.bak'
  월 15:00	BACKUP LOG Sample TO DISK='F:\DBBackup\sample_log.bak'

  [따라하기] 문제가 발생하여 전체 데이터베이스 백업과 로그 백업으로 복구하기
  트랜잭션 로그는 로그 백업 이후의 변경된 자료만을 가지고 있기 때문에, 복원할 경우에는 모든 로그 파일이 순차적으로 필요합니다.

  1. NO_TRUNCATE 절을 사용하여 BACKUP LOG 문을 실행함으로써 현재 활성화된 트랜잭션 로그를 백업합니다. <PRE>BACKUP LOG SampleTO DISK='F:\DBBackup\sample_log2.bak'WITH NO_TRUNCATEGO</PRE>2. 장애가 발생하기 전의 마지막 전체 백업을 복원합니다. <PRE>USE masterGORESTORE DATABASE SampleFROM DISK= 'F:\DBBackup\sample.bak'WITH FILE = 1, NORECOVERYGO</PRE>3. 복원한 전체 백업 이후, 첫 번째 로그 백업을 복원합니다. <PRE>RESTORE LOG SampleFROM DISK='F:\DBBackup\sample_log.bak'WITH FILE = 1, NORECOVERYGO</PRE>4. 순차적으로 다음 로그 백업을 차례로 복원합니다. <PRE>RESTORE LOG SampleFROM DISK='F:\DBBackup\sample_log.bak'WITH FILE = 2, NORECOVERYGO</PRE>5. 단계1에서 백업받은 로그 백업을 복원합니다. (백업이 성공한 경우) <PRE>RESTORE LOG SampleFROM DISK='F:\DBBackup\sample_log2.bak'WITH RECOVERYGO</PRE>

  [참고] 복구 모델이 "대량 로그 복구"일 경우에는, SELECT INTO 등과 같은 대량 로그 작업은 복원할 수 없습니다.

• 전체 백업과 파일 그룹 백업을 실행한 경우의 복원하기

  백업 일시	백업
  월 05:00	BACKUP DATABASE Sample TO DISK='F:\DBBackup\sample.bak' WITH INIT
  화 05:00	BACKUP DATABASE sample Primary TO DISK='F:\DBBackup\sample_prm.BAK'
  화 17:00	BACKUP LOG Sample TO DISK='F:\DBBackup\sample_log.BAK'
  수 05:00	BACKUP DATABASE Sample Secondary TO DISK='F:\DBBackup\sample_scn.BAK'
  수 17:00	BACKUP LOG Sample TO DISK='F:\DBBackup\sample_log.BAK'

  
  

  [따라하기]
  위의 백업을 실행 후에, Secondary 파일 그룹이 깨졌다고 가정합니다. 전체 백업을 복구할 필요 없이, 파일 그룹 백업만으로 복구가 가능합니다. 대용량 데이터베이스일 경우, 파일 그룹 백업은 복원 시간 단축에 매우 효과적입니다.

  1. Secondary 백업을 복원합니다. <PRE>RESTORE DATABASE Sample SecondaryFROM DISK=' F:\DBBackup\sample_scn.bak'WITH FILE = 1, NORECOVERYGO</PRE>2. 복원한 백업 이후의, 로그 백업을 순차적으로 복원합니다. <PRE>RESTORE LOG SampleFROM DISK='F:\DBBackup\sample_log.bak'WITH FILE = 2, RECOVERYGO</PRE>

• 파일 위치 지정하여 복원하기

  [따라하기]
  sample_dat 데이터 파일은 "c:\data\"에, sample_log 로그 파일은 "d:\log\"로 위치를 변경하여 복원하고자 한다면, MOVE … TO 옵션을 사용하여 파일의 위치를 지정하면 됩니다.

  <PRE>RESTORE DATABASE Sample FROM DISK='F:\DBBAckup\Sample.BAK'WITH MOVE 'sample_dat' TO 'c:\data\sample_dat.mdf', MOVE 'sample_log' TO 'd:\log\sample_log.ldf', REPLACEGO</PRE>

  [참고] REPLACE 옵션은 지정한 위치에 같은 파일이 이미 존재할 때 사용합니다.

• 지정 시간 복구하기

  지정 시간 복구는 오직 트랜잭션 로그 백업 상태에서만 가능합니다. RESTORE 명령어에 STOPAT 옵션을 사용하면 날짜와 시간을 정하여 데이터베이스를 복구할 수 있습니다. 이 경우 DBA는 사용자로부터 오류가 발생한 정확한 날짜와 시간을 알아내야 합니다. STOPAT 옵션은 정확하지 않은 데이터를 테스트하기 위하여 NORECOVERY 옵션과 함께 사용할 수가 없습니다. 정확한 시간이 필요합니다. RESTORE 문에 기술된 날짜와 시간 이전에 커밋되지 않은 트랜잭션은 롤백될 것이며 이는 데이터의 손실을 초래합니다.

  [따라하기] 2004년 12월 30일 오전 12시 상태로 데이터베이스를 복원하고 여러 로그와 여러 백업 장치와 관련된 복원 작업입니다.

  <PRE>USE masterGORESTORE DATABASE Sample FROM DISK='F:\DBBackup\sample.bak' WITH FILE = 1, NORECOVERYRESTORE LOG Sample FROM DISK='F:\DBBackup\sample_log.bak' WITH FILE = 1, NORECOVERYRESTORE LOG Sample FROM DISK='F:\DBBackup\sample_log.bak' WITH FILE = 2, RECOVERY, STOPAT = '2004-12-30 15:36:00.000'GO</PRE>
• 표시된 트랜잭션 복구하기

  표시된 트랜잭션은 DBA가 잘못된 트랜잭션이 발생한 시점을 확인하는데 있어 유용하며, 보다 쉽게 복구를 할 수 있도록 해 줍니다. WITH MARK 옵션을 사용하면 트랜잭션 이름이 트랜잭션 로그에 저장되며, 이 옵션을 사욛하면 날짜와 시간 대신 표시된 트랜잭션을 사용하여 데이터베이스를 이전 상태로 복원할 수 있습니다.
  로그에서 표시로 복구하는 방법은 다음 두 가지가 있습니다.
  RESTORE LOG와 WITH STOPATMARK='mark_name' 절을 사용하여 표시된 부분까지 롤포워드하고 표시가 있는 트랜잭션을 포함시킵니다.
  RESTORE LOG와 WITH STOPBEFOREMARK='mark_name' 절을 사용하여 표시된 부분까지 롤포워드하고 표시가 있는 트랜잭션은 제외시킵니다.
  WITH STOPATMARK와 WITH STOPBEFOREMARK 절은 선택적인 AFTER datetime 절을 지원합니다. AFTER datetime이 생략되면 지정한 이름이 있는 첫 번째 표시 지점에서 복구가 중지됩니다. AFTER datetime이 지정되면 지정한 일시 또는 지정한 시점 이후에 지정한 이름이 있는 첫 번째 표시 지점에서 복구가 중지됩니다.

  [따라하기]

  <PRE>/* 트랜잭션 표시 */BEGIN TRANSACTION UpdateCol3 WITH MARK 'Update Col3 values'GOUPDATE Tab_SampleSET Col3 = Col3 * 100 GOCOMMIT TRANSACTION UpdateCol3GO/* 표시된 트랜잭션 복원 */USE masterGORESTORE DATABASE Sample FROM DISK='F:\DBBackup\sample.bak' WITH FILE = 1, NORECOVERYRESTORE LOG Sample FROM DISK='F:\DBBackup\sample_log.bak' WITH FILE = 1, STOPATMARK = 'UpdateCol3'GO</PRE>

스크립트 백업

• 오브젝트 스크립트 백업하기

  [따라하기]

  1. 엔터프라이즈 관리자에서 스크립트를 생성하고자 하는 데이터베이스를 선택하고 마우스의 오른쪽 버튼을 클릭하여, [모든 작업] ' [SQL 스크립트 생성]을 선택합니다.

  

  2. SQL 스크립트 생성 창의 [모두 표시]를 클릭하고, 필요한 경우 스크립트 백업 받기를 원하는 오브젝트 종류를 선택합니다. 테이블 스크립트를 저장하려고 한다면, [모든 테이블]을 선택합니다.

  

  3. [서식] 탭을 선택합니다. [개체마다 DROP <개체>명령 생성]의 선택을 제거할 것을 권고합니다. 생성된 SQL 스크립트를 실수로 운영 DB서버에서 수행하여 운영중인 오브젝트들이 모두 삭제되는 불상사가 간혹 발생하고 있으므로, 항상 DROP 옵션은 체크 해제한 상태에서 스크립트를 받을 것을 권고합니다.

  

  4. [옵션] 탭을 선택합니다. 필요한 옵션을 선택하고, [확인]을 클릭합니다. 테이블의 경우에는 일반적으로 제약 조건과 인덱스 스크립팅을 선택합니다. 예를 들어 모데이터베이스 내 모든 저장 프로시저들의 스크립트를 받고자 하는 경우에 [개체마다 파일 하나씩 만들기] 옵션을 선택하기도 하는데, 저장 프로시저의 수가 매우 많은 경우에는 이 옵션은 성능 문제를 유발할 수 있으므로 작업 부하가 가장 적은 시점에 사용하기 바랍니다.

  

  5. 저장할 파일명을 입력하고, [저장]을 클릭합니다.

• JOB 스크립트 백업하기

  1. EM의 [관리] ' [SQL Server에이전트] ' [작업] 위에서 마우스 오른쪽 버튼을 클릭하여 [모든 작업]'[SQL스크립트 생성]을 선택합니다.

  

  2. SQL 스크립트 생성 창의 파일 이름을 입력하고, SQL 생성 옵션의 [작업이 있으면 바꾸기]의 선택을 제거합니다.

  

  3. [확인]을 클릭합니다.