SQL Server DBA 가이드

DBA라면 이 정도는 알고 있어야 하지 않을까요 !!!

이 페이지의 내용

데이터베이스 관리 백업과 복구 테이블 관리 시스템 오브젝트 생성 사용자 관리 서버 및 데이터베이스의 정보 확인 성능 모니터링 프로필러 문제 점검 및 해결

DBA의 역할과 책임

DBA의 역할

시스템과 조직에 따라 DBA의 임무에 차이가 있을 수 있지만 일반적으로 대부분의 DBA는 다음과 같은 작업들을 책임지고 수행해야 하는 임무를 가집니다.

• 설치와 환경설정
  - 소프트웨어 설치
  - 환경 설정
  
• 보안 관리
  
• 운영
  - 백업과 복원
  - 사용자 관리
  - 기타 일상적인 운영 업무
  
• 서비스 레벨 유지
  - 성능 최적화 및 성능 모니터링
  - 용량 계획 (Capacity Planning)
  
• 시스템 가동 시간 관리
  - 시스템 정지 시간의 계획과 일정 관리
  
• 문서화 작업
  
• 작업 절차 계획 및 규격화
  - 운영 유지보수 계획 수립
  - 재난 복구 계획 수립
  
• 설계 및 개발 지원
  - 데이터 모델링
  - 데이터베이스 설계
  - 저장 프로시저 개발
  - 응용 프로그램 개발
  
• 개발 환경 관리
  - 개발 시스템 환경 별도 제공 및 개발 시스템 관리
  
• 긴급 상황 해결/장애 복구
  
• SQL Server 관리에 필요한 지식 숙지
  

DBA 작업의 기본적인 원칙

DBA가 시스템 유지를 위하여 일반적으로 수행하는 모든 작업들에 대하여 기본적으로 다음과 같은 원칙에 의거하여 작업할 것을 권고합니다.

작업 표준화 체계 수립

표준화는 관리에 있어서 매우 중요한 요소입니다. 자신의 시스템에 가장 적합한 표준화 체계를 수립하고, 전체 시스템에 대하여 표준화된 관리 체계를 적용하여 관리해야 합니다. 예를 들어, 다중의 DB 서버를 관리하는 경우에는 표준화가 특히 중요합니다.

문서화

DB 관리와 같이 중요한 작업은 사람의 기억에 의한 주먹구구식의 작업이 되어서는 안됩니다. 어떤 경우라도 항상 정확하고 일관된 작업이 가능하도록 문서화가 필요합니다. 기록 가능한 모든 작업들에 대해서 문서화하고, 변경이 발생하면 지속적으로 업데이트하는 관리가 필요합니다.

• 작업 매뉴얼 : 작업 수행 절차에 대한 정보 (설치, 장애 복구, 백업과 복원 전략, 주기적으로 수행하는 작업 등에 대한 작업 절차 및 참고 사항이 이에 포함될 수 있으며, 일반적이고 중요한 정보는 운영 매뉴얼에 기록하여 모든 DBA가 참조할 수 있도록 합니다.)
• 시스템 환경에 대한 정보 : 서버의 하드웨어, 소프트웨어, 네트워크 등에 대한 정보
• 담당자 및 관계자에 대한 정보 : 시스템과 관련된 내/외부 조직에 포함되는 모든 사람과 하드웨어/소프트웨어 제품 및 서비스 공급업체 및 담당자에 대한 정보
• 장애 기록 일지 : 발생된 문제와 문제 해결에 관한 모든 절차에 대한 기록 (장애 기록에 대한 내용은 활용 및 검색이 용이하도록 웹 기반으로 만들어, 유사한 문제의 재발 시에 신속하게 처리할 수 있도록 합니다.)

스크립트화

반복적, 주기적으로 수행하는 모든 작업들은 엔터프라이즈 관리자를 사용하는 대신, 스크립트를 작성하여 수행하는 것을 원칙으로 합니다. 스크립트를 사용하면 오류 발생 가능성을 최소화할 수 있으며 반복적인 작업을 효율적으로 수행할 수 있습니다. 스크립트는 보안을 위하여 안전한 디렉터리에 중앙 집중적으로 관리하는 것이 바람직하며, 스크립트 작성 시에는 응용 프로그램과 마찬가지로 주석을 기술하여 쉽게 이해하고 활용할 수 있도록 합니다. 만약 주석만으로 불충분한 경우에는 문서를 작성하여 관리합니다.

자동화

주기적으로 수행해야 하는 작업들은 가능한 한 자동화하여 DBA의 업무 효율성을 제고할 것을 권고합니다. 예를 들어 DB 서버 성능 데이터의 수집, 디스크 공간의 확인, 백업, 블로킹 감지, 데이터 타입 오버플로우 감지 등의 작업들은 자동화가 가능합니다. 단순히 수행을 자동화하는 차원을 넘어서, SQL Server에서 제공하는 다양한 기능들을 활용하면 자동으로 경고 메일의 발송, 문자 메시지의 발신, 문제 해결을 위한 작업의 수행 등이 가능하기 때문에, DBA가 지속적으로 시스템을 모니터링하지 않더라도 시스템에 발생한 문제를 조기에 감지하는 것이 가능합니다. DBA가 주기적으로 수행되는 작업에 할애하는 시간은 가능한 한 최소화하고, 주기적인 관리 작업을 통하여 확보한 지식을 기반으로 응용 프로그램과 서버의 성능을 향상시키기 위한 전략을 모색하는데 많은 시간을 할애하는 것이 바람직합니다.

신중한 변경 관리 및 롤백 전략 수립

운영중인 시스템에 어떤 변경작업을 수행하는 경우에는 가능한 한 충분한 사전 테스트를 거친 후에 작업해야 하며, 롤백 전략을 수립한 다음에 작업하는 것을 원칙으로 합니다. 또한 한번에 여러 가지 변경 작업을 수행하지 말고, 하나의 변경 작업을 수행하고 그 변경 작업이 미친 영향을 관찰하는 것이 바람직합니다.
모든 변경 작업에 대해서 롤백 전략을 수립하는 것이 원칙이며, 롤백에 필요한 사항들을 문서로 기록하고 롤백에 필요한 스크립트 등을 작성하고 테스트하여 검증합니다. 특히 대용량 데이터베이스의 경우에는 문제 발생 시 복구에 소요되는 시간이 길기 때문에 충분한 사전 테스트와 롤백 전략 수립이 매우 중요합니다.

DBA가 주기적으로 수행해야 하는 작업

시스템에 따라 차이가 있을 수 있지만, DBA는 시스템 유지를 위하여 일반적으로 수행해야 하는 작업들에 대하여 이해하고 있어야 하며, 다음과 같은 작업들을 주기적으로 수행해야 합니다.

일 단위로 수행해야 하는 작업

• 시작되어야 할 서비스들이 제대로 시작되어 있는지 확인합니다.
• Windows NT 또는 Windows 2000의 이벤트 뷰어를 사용하여 오류 발생 여부를 점검합니다.
• SQL Server 오류 로그에 오류 메시지가 기록되어 있는지 점검합니다. 자세한 내용은 [SQL Server 오류 로그 보기]를 참조하십시오.
• 데이터베이스 파일과 로그 파일의 확장에 대비하여 디스크에 충분한 여유 공간이 있는지 확인합니다.
• 데이터베이스 파일과 로그 파일의 크기와 실제로 사용되는 공간을 모니터링하며, 공간 부족으로 자동 확장이 예상되는 경우에는 미리 파일을 확장하여 충분한 공간을 확보합니다.
• SQL Server 작업(Job)의 성공/실패 여부를 점검합니다.
• 매일 데이터베이스 전체 백업 또는 차등 백업을 수행하기로 되어 있는 경우라면, 데이터베이스 전체 백업을 수행합니다. 자동화되어 있는 경우에는 백업이 성공적으로 수행되었는지 점검합니다. 데이터베이스 전체/차등 백업 주기는 시스템 여건과 복원 전략에 따라 달라집니다.
• SQL Server 트랜잭션 로그를 백업 받습니다. 자동화되어 있는 경우에는 백업이 성공적으로 수행되었는지 점검합니다. 백업 주기는 시스템 여건에 따라 백업 주기는 분 단위, 시간 단위, 일 단위로 달라질 수 있으며, 트랜잭션 백업 주기에 따라 트랜잭션 로그 파일의 크기가 달라집니다. 참고로 복원이 불필요한 테스트 DB에 대해서는 복구 모델을 단순으로 설정하면 트랜잭션 로그에 대한 주기적인 관리를 줄일 수 있습니다.
• Master, model, msdb, 배포(distribution) 데이터베이스도 변경 사항이 있으면 주기적으로 백업해야 합니다. 시스템 카탈로그의 변경이 이루어진 후에는 master 데이터베이스의 전체 백업을 수행합니다. 경고, 작업(Job), 운영자, 로그 전달(log-shipping), 복제, DTS 패키지 등에 변경이 발생한 다음에는 msdb를 백업해야 합니다. Model 데이터베이스에 변경작업을 수행한 다음에는 model을 백업해야 합니다.
• 시스템 모니터를 사용하여 성능 카운터를 모니터링함으로써, 적절한 성능이 유지되고 있는지 점검합니다. 최소한 시스템 모니터에서 프로세서, 메모리, 디스크(I/O), 네트워크에 대한 카운터들은 필수로 점검해야 합니다. 문제 발생 시 또는 추가적인 분석이 필요한 경우에는 관련 성능 카운터들을 추가로 분석합니다.
• 복구 모델이 전체 복구가 아니라면, 최소 로깅 작업(Minimal-logged operation)을 수행한 다음에는 차등 백업을 수행합니다.
• 블로킹, 교착상태(Deadlock)의 발생 여부를 점검합니다.
• 오래 수행되는 쿼리 또는 리소스를 과다하게 사용하는 쿼리가 있는지 점검합니다.
• 문제가 발생하면 문제 해결을 위한 활동을 수행하며, 문제 분석 및 해결 과정에 대한 내용을 가능한 한 상세하게 문서화합니다.
• 통계 자동 갱신(Auto update statistics) 옵션이 비활성화되어 있는 데이터베이스의 테이블들에 대해서는 주기적으로 (예:매일, 매주) UPDATE STATISTICS 작업을 수행합니다.

주간 단위로 수행해야 하는 작업

• 모든 시스템 데이터베이스와 운영중인 사용자 데이터베이스에 대한 전체/차등 데이터베이스 백업을 수행합니다.
• 통계 자동 갱신(Auto update statistics) 옵션이 비활성화되어 있는 데이터베이스의 테이블들에 대해서 UPDATE STATISTICS를 매일 또는 매주 수행합니다.
• 인덱스의 조각화를 제거합니다. CREATE INDEX WITH DROP_EXISTING 또는 DBCC DBREINDEX를 수행하여 인덱스를 재구성함으로써 물리적, 논리적 조각화를 제거할 수 있으며, DBCC INDEXDEFRAG를 사용하면 논리적인 조각화를 제거할 수 있습니다. 자세한 내용은 온라인 설명서를 참조하십시오.
• 대형 일괄 처리의 작업 등으로 인하여 로그 파일이 과다하게 확장된 경우에는 로그 파일의 사용되지 않는 여분의 공간을 제거합니다.

월간 단위로 수행해야 하는 작업

• 전체 운영 체제를 백업합니다.
• 최소 월 1회 모든 시스템 데이터베이스와 운영 데이터베이스에 대하여 전체 백업을 수행해야 합니다.
• DBCC CHECKDB를 수행하여 데이터베이스의 무결성을 점검합니다. DBCC CHECKDB를 수행하면 서비스나 다른 작업에 영향을 미칠 수 있으므로, 테스트 장비에 모든 시스템 데이터베이스와 운영 데이터베이스를 복원하고, 복원된 모든 시스템 데이터베이스와 운영 데이터베이스를 대상으로 DBCC CHECKDB를 수행하여 무결성을 점검하는 것이 바람직합니다.
• Sqldiag.exe를 수행하고 결과를 저장합니다.
• 성능 데이터를 수집하여 시스템이 충족시켜야 하는 기준과 비교하여, 성능 향상 및 향후의 용량 계획에 활용합니다.

[참고] 정확한 점검을 위해서는 모든 유지 관리 활동 작업에 대하여 로그를 저장하는 것이 필요합니다. 데이터베이스 유지 관리 계획 마법사와 SQL Server 작업(Job)에서는 자동으로 작업 결과를 저장하도록 설정 가능합니다.

데이터베이스 관리

데이터베이스 생성

번호	수칙	체크
1	트랜잭션 로그 파일은 로그 전용 드라이브에 배치합니다.
2	트랜잭션 로그 파일을 저장할 디스크는 일반적으로 RAID10으로 구성합니다.
3	데이터베이스를 만들 때 향후 예상되는 최대 데이터 크기를 고려하여 충분한 크기로 생성합니다.
4	파일이 증가할 수 있는 최대 크기를 지정하는 것을 권고합니다.
5	파일이 자동으로 증가하도록 설정하는 경우에는 자동 확장 증가 크기를 적절하게 설정합니다.
6	파일 그룹을 사용하여 데이터를 배치합니다.
7	데이터베이스를 생성한 경우에는 master 데이터베이스를 백업합니다.
8	tempdb는 I/O가 빠른 쪽에 배치할 것을 권고합니다.

수칙1. 트랜잭션 로그 파일은 로그 전용 드라이브에 배치합니다.

데이터 파일들과 트랜잭션 로그 파일은 서로 다른 디스크에 배치합니다.
모든 데이터베이스는 최소 하나의 주 데이터 파일(Primary Data File)과 하나의 트랜잭션 로그 파일로 구성됩니다. 트랜잭션 로그 파일은 별도의 드라이브에 배치합니다.

[따라하기] 주 데이터 파일은 D 드라이브에 배치하고 트랜잭션 로그 파일은 E 드라이브에 배치하는 데이터베이스 생성하기

확장명은 주 데이터 파일은 .mdf, 보조 데이터 파일은 .ndf, 트랜잭션 로그 파일은 .ldf를 사용합니다.

<PRE>USE masterGOCREATE DATABASE sample /* 데이터베이스 이름 */ON (NAME = sample_dat, /* 데이터 파일 이름 */FILENAME = 'd:\DBdata\sample_dat.mdf', /* 데이터 파일 위치 */SIZE = 100 MB, /* 데이터 파일 초기 크기 */MAXSIZE = 1 GB, /* 데이터 파일 최대 크기 */FILEGROWTH = 100 MB) /* 데이터 파일 증가량 */LOG ON (NAME = sample_log, /* 로그 파일 이름 */FILENAME = 'e:\DBlog\sample_log.ldf', /* 로그 파일 위치 */SIZE = 20 MB, /* 로그 파일 초기 크기 */MAXSIZE = 500 MB, /* 로그 파일 최대 크기 */FILEGROWTH = 50 MB) /* 로그 파일 증가량 */GO</PRE>

수칙2. 트랜잭션 로그 파일을 저장할 디스크는 일반적으로 RAID10으로 구성합니다.

트랜잭션 로그 파일의 경우에는 복제가 구성되어 있거나 트리거가 빈번하게 수행되는 경우가 아니라면 대부분의 IO가 쓰기 작업이므로, 쓰기 작업의 성능을 위하여 트랜잭션 로그 파일은 RAID 10에 저장할 것을 권고합니다. 참고로, 로그에서 대기가 발생하는지는 다음 명령어로 확인할 수 있습니다.

<PRE>DBCC SQLPERF (WAITSTATS)GO</PRE>

수칙3. 데이터베이스를 만들 때 향후 예상되는 최대 데이터 크기를 고려하여 충분한 크기로 생성합니다.

주 데이터 파일, 트랜잭션 로그 파일 모두 충분한 크기로 생성합니다. 파일이 증가하는 동안에는 쓰기 작업은 대기 상태가 되기 때문에 잦은 확장은 성능에 좋지 않은 영향을 미칠 수 있습니다. 트랜잭션 로그를 자동 증가하도록 옵션을 설정하되, 되도록이면 로그의 사이즈가 증가될 필요가 없도록 합니다. 트랜잭션 로그의 초기 크기는 트랜잭션 로그 백업을 수행한 후, 다음 로그 백업이 수행되기 전까지 발생하는 작업들을 저장하기에 충분한 크기로 생성합니다. 트랜잭션 로그 파일에 대하여 여러 번의 자동 증가가 발생하게 되면, 여러 개의 가상 로그 파일들로 조각화되어, 로그 관련 작업의 성능에 좋지 않은 영향을 미칩니다. 가상 로그 파일을 줄이는 방법은 [데이터 베이스 축소하기]를 참조하십시오.
만약 데이터베이스의 초기 크기가 작아서 확장이 발생하고 있다면, 파일의 크기를 충분한 크기로 확장하기 바랍니다.

[따라하기] 데이터베이스 파일 확장하기

<PRE>ALTER DATABASE SampleMODIFY FILE (NAME = sample_dat, MAXSIZE = 1 GB)GO</PRE>

수칙4. 파일이 증가할 수 있는 최대 크기를 지정하는 것을 권고합니다.

파일의 최대 크기를 지정하면, 파일의 크기가 증가하여 디스크 여유 공간이 전혀 없는 상태가 되는 것을 방지할 수 있습니다. 파일의 최대 크기를 지정하려면 CREATE DATABASE 문의 MAXSIZE 매개 변수를 사용하거나 엔터프라이즈 관리자의 등록 정보 대화 상자의 파일 증가 제한(MB) 옵션을 사용하면 됩니다.
만약 기존의 데이터베이스 파일의 최대 크기가 UNLIMITED로 설정되어 있다면, 최대 크기를 설정하기 바랍니다.

[따라하기] 데이터베이스 파일의 최대 크기 확인 및 설정하기

<PRE>EXEC sp_helpdb Sample -- 또는 EXEC Sample..sp_helpfileGO-- 결과 중 maxsize 정보를 확인 후 다음 명령어를 수행합니다.ALTER DATABASE SampleMODIFY FILE (NAME = sample_dat, SIZE = 200MB)GO</PRE>

수칙5. 파일이 자동으로 증가하도록 설정하는 경우에는 자동 확장 증가 크기를 적절하게 설정합니다.

파일의 크기가 매우 작거나 매우 큰 경우에는 파일 자동 확장 증가분을 퍼센트 단위가 아닌 MB 단위로 지정하는 것을 권고합니다. 파일의 자동 확장 증가분을 지정하지 않으면 디폴트 값이 10% 확장으로 설정되는데, 데이터베이스의 크기가 큰 경우에는 새로운 데이터를 저장할 공간이 없어서 자동 확장이 이루어질 때 소요시간이 오래 걸림으로 인하여 트랜잭션 로그를 발생시키는 작업들이 대기 또는 실패하는 문제가 발생할 수 있습니다. 예를 들어, 데이터 파일의 크기가 100GB인 경우에 파일의 자동 확장 증가분이 10%로 설정되어 있다면, 자동 확장이 발생할 경우 10GB의 파일 확장을 수행합니다. 10GB의 확장작업은 상당한 시간이 걸리는 작업이므로 정상적인 서비스를 하지 못하는 문제를 유발할 수 있습니다. 반대로 파일의 크기가 매우 작은 경우에는 10%씩 증가하면 확장되는 크기가 작아서 빈번하게 재확장이 발생합니다. 로그 파일의 경우에 작은 크기의 확장이 여러 번 발생하면 여러 개의 작은 가상 로그 파일(VLF)들로 단편화가 발생하게 되어 성능을 저하시킬 수 있으므로 유의하기 바랍니다. 가상 로그 파일의 수는 일반적으로 25개 미만으로 유지하는 것을 권고하며, 가상 로그 파일의 수가 지나치게 많은 경우에는 가상 로그 파일의 수를 줄이는 작업을 수행하는 것이 좋습니다. 작업 방법은 [트랜잭션 로그 파일 축소하기]를 참조하십시오.

[따라하기] 데이터베이스의 데이터 파일 증가율 100MB로 변경하기

<PRE>ALTER DATABASE SampleMODIFY FILE (NAME = sample_dat, FILEGROWTH = 100MB)GO</PRE>

수칙6. 파일 그룹을 사용하여 데이터를 배치합니다.

주 데이터 파일에는 메타 데이터만 저장하고, 사용자 오브젝트들은 사용자 정의 파일 그룹에 저장하며, 디폴트 파일 그룹을 주 파일 그룹이 아닌 사용자 정의 파일 그룹으로 변경할 것을 권고합니다. 참고로 데이터베이스는 주 파일 그룹과 사용자 정의 파일 그룹으로 구성되며 주 파일이 있는 파일 그룹이 주 파일 그룹이 되고 주 파일 그룹에는 모든 시스템 테이블이 저장됩니다. 데이터베이스에 오브젝트를 만들 때 파일 그룹을 지정하지 않으면 오브젝트들은 디폴트 파일 그룹에 저장되며 디폴트로 주 파일 그룹이 디폴트 파일 그룹이 됩니다.

[따라하기] 파일 그룹이 있는 데이터베이스 생성하기

<PRE>USE masterGOCREATE DATABASE sample2ON Primary ( /* PRIMARY 파일 그룹 */NAME = sample_pri_dat, FILENAME = 'D:\DBdata\sample_pri_dat.mdf',SIZE = 200 MB, MAXSIZE = 1 GB, FILEGROWTH = 20 MB), FILEGROUP SamplesFG1 /* 두 번째 파일 그룹 */(NAME = sample1_dat, FILENAME = 'E:\DBdata\sample1_dat.ndf', SIZE = 200 MB, MAXSIZE = 1 GB, FILEGROWTH = 20 MB), FILEGROUP SamplesFG2 /* 세 번째 파일 그룹 */(NAME = sample2_dat, FILENAME = 'F:\DBdata\sample2_dat.ndf', SIZE = 200 MB, MAXSIZE = 1 GB, FILEGROWTH = 20 MB) LOG ON ( /* 로그 파일 */NAME = sample_log, FILENAME = 'G:\DBlog\sample_log.ldf', SIZE = 10 MB, MAXSIZE = 50 MB,FILEGROWTH = 5 MB) GO</PRE>

[따라하기] 디폴트 파일 그룹 확인 및 변경하기

<PRE>USE Sample2SELECT * FROM sysfilegroups WHERE status = 16GO/* 'Primary' 파일 그룹이 디폴트 파일 그룹이면 1이 반환되고 그렇지 않으면 0이 반환됩니다. */SELECT FILEGROUPPROPERTY('Primary', 'IsDefault')GO/* 디폴트 파일 그룹을 'SamplesFG1'로 변경합니다. */ALTER DATABASE Sample2 MODIFY FILEGROUP [SamplesFG1] DEFAULTGO</PRE>

수칙8. tempdb는 I/O가 빠른 쪽에 배치할 것을 권고합니다.

Tempdb는 I/O가 빠른 쪽에 배치하는 것이 성능을 위해 좋습니다. Tempdb를 여러 디스크에 스트라이핑하면 더욱 좋습니다. 또한 tempdb를 자주 쓰는 사용자 데이터베이스와 물리적으로 격리된 디스크에 배치할 것을 권고합니다. 특히 tempdb를 매우 많이 사용하는 대규모 시스템이라면 tempdb를 별도의 디스크 세트에 배치하면 더 나은 성능 향상을 기대할 수 있습니다. 유의할 사항은, 데이터베이스 데이터와 운영 시스템의 페이징 파일을 동일한 디스크에 배치하는 것은 어떤 경우라도 좋은 방법이라고 할 수 없습니다.

[참고] 그 외 파일 배치하기
운영 시스템은 RAID 1로 구성된 어레이에 있어야 합니다. 페이징 파일은 운영 시스템이 있는 드라이브에서 훨씬 잘 동작하며, 데이터베이스에 별도의 디스크를 할당할 수 있게 하기 위해서 같은 위치에 있어도 관계 없습니다. 페이징 파일을 옮겨야 할 필요가 있다면, 데이터베이스의 데이터, 로그, tempdb가 있는 곳에는 위치시키지 않아야 합니다. 이렇게 해야 디스크 오류에 빠르게 대응하여 복구할 수 있습니다. 이 때, 부트 디스크는 미러를 이용하여 부팅 가능해야 합니다.
시스템 백업을 동일 서버에 저장해야 한다면 반드시 데이터나 로그 파일이 없는 다른 디스크에 저장해야 합니다.
파일 그룹에 파일의 개수는 SQL Server의 성능과 관련이 없으므로, 파일을 관리하기 쉽게 배치합니다.

[참고] CREATE DATABASE가 실패하는 경우 문제 해결하기
새로운 데이터베이스의 생성이 실패하는 원인에는 여러 가지가 있지만 주로 다음과 같은 문제로 인하여 새로운 데이터베이스의 생성이 실패합니다.

1. model 데이터베이스가 사용 중일 때
   model 데이터베이스를 사용하는 프로세스의 수행이 완료되기를 기다렸다가 재수행하거나, model 데이터베이스를 사용 중인 프로세스를 강제로 중지한 후에 재수행합니다.
   
   
2. 데이터베이스 파일의 물리적인 위치를 잘못 지정했을 때
   지정한 폴더가 실제로 있는지 확인합니다.
   지정한 드라이브에 충분한 여유 공간이 있는지 확인합니다.
   
   
3. CREATE DATABASE를 수행한 사용자에게 새로운 데이터베이스를 생성할 수 있는 권한이 없을 때
   새로운 데이터베이스를 생성하기 위해서는 sysadmin 또는 dbcreator 역할의 구성원이어야 합니다. 이 역할의 구성원에게 작업을 요청하거나, 주기적으로 작업이 필요하다면 해당 사용자를 dbcreator 역할에 추가하면 됩니다.
   
   
4. 동일한 이름의 데이터베이스가 이미 존재할 때
   sp_helpdb를 수행하거나 master..sysdatabases 테이블을 참조하여 확인합니다. 만약 기존의 데이터베이스가 불필요하다면 sp_renamedb 를 사용하여 기존의 데이터베이스를 다른 이름으로 변경하거나 삭제한 후에 다시 시도합니다.
   
   
5. 동일한 이름의 파일이 이미 존재할 때
   존재하지 않는 파일 이름을 지정하고 다시 시도합니다.

데이터베이스 삭제하기

<PRE>[구문]USE masterGODROP DATABASE database_name [ ,...n ]GO</PRE>

[유의사항]
DROP DATABASE를 수행하면 모든 데이터베이스 파일들도 디스크에서 삭제됩니다. 만약 다시 복구하고자 하는 경우에는 백업본을 복원해야 합니다. 그러므로, 만약 다시 참조할 필요가 있는 데이터베이스를 삭제하고자 하는 경우에는 sp_detach_db를 사용하여 SQL Server에서 데이터베이스 정보만 삭제하고 파일들은 디스크에 남겨 둘 것을 권고합니다. [데이터베이스 파일 위치 변경하기]를 참조하십시오.
데이터베이스를 삭제하면 master 데이터베이스의 시스템 테이블이 업데이트 되므로 데이터베이스가 삭제된 후에는 master 데이터베이스를 백업할 것을 권고합니다. master 데이터베이스를 복원할 필요가 있을 때, 마지막 master 백업 이후 삭제된 데이터베이스가 시스템 테이블에 남아 있으면 그로 인하여 오류가 발생할 수 있습니다.

[참고] DROP DATABASE가 실패하는 경우 문제 해결하기
삭제하고자 하는 데이터베이스를 다른 프로세스에서 연결 중이면 데이터베이스를 삭제할 수 없습니다. 데이터베이스 사용 중이어서 삭제할 수 없는 경우에는, 해당 데이터베이스를 사용하는 프로세스들이 완료되기를 기다렸다가 삭제하거나 아니면 다음과 같이 데이터베이스를 단일 사용자 모드로 변경한 다음에 삭제하거나 또는 spid를 확인하여 KILL 명령어로 프로세스들을 중지한 다음에 재시도합니다.

<PRE>USE masterGOALTER DATABASE SampleSET SINGLE_USER WITH ROLLBACK AFTER 30 -- 30초가 경과한 후에 롤백 GO</PRE>

데이터베이스 이전하기

[따라하기] 사용자 데이터베이스 이전하기 C 드라이브에 주 데이터 파일과 트랜잭션 로그 파일이 있는 데이터베이스를 주 데이터 파일은 D 드라이브, 트랜잭션 로그 파일은 E 드라이브로 이전합니다.

데이터베이스명	SAMPLE
데이터 파일명	sample_dat
변경 전 데이터 파일 위치	C:\data\sample_dat.mdf
변경 후 데이터 파일 위치	D:\data\sample_dat.mdf
로그 파일명	sample_log
변경 전 로그 파일 크기	C:\log\sample_log.ldf
변경 후 로그 파일 위치	E:\log\sample_log.ldf

1. 데이터베이스에 연결되어 있는 연결을 모두 비 연결 상태로 만들고, 단일 사용자 모드로 설정합니다. 다음은 5초 후에 모든 작업들이 ROLLBACK되고, 연결을 끊는 예제입니다. <PRE>USE masterGOALTER DATABASE SampleSET SINGLE_USER WITH ROLLBACK AFTER 5GO</PRE>[참고] <PRE>EXEC sp_dboption database_name, 'single user', trueGO</PRE>이 작업을 실행하는 것 외에 다른 사용자가 연결을 하고 있을 경우에는, 데이터베이스 상태를 변경할 수 없습니다. 이 방법을 사용 하는 경우에는, DBA가 모든 작업들의 연결을 끊은 후에, 이 작업을 해야 합니다.
2. 해당 데이터베이스의 모든 데이터 파일과 트랜잭션 로그파일의 경로의 확인합니다. <PRE>EXEC sp_helpdb SampleGO</PRE>
3. 데이터베이스와 파일을 분리합니다. <PRE>EXEC sp_detach_db 'Sample', 'true'GO</PRE>
4. 데이터베이스 파일들을 원하는 위치에 복사합니다.
   C 드라이브에 있는 sample_dat.mdf, sample_log.ldf를 각각 d:\data, e:\log 밑에 복사합니다.
5. 새로운 위치의 파일을 지정하여 데이터베이스와 연결합니다. <PRE>EXEC sp_attach_db 'Sample','d:\data\sample_dat.mdf' ,'e:\log\sample_log.ldf'GO</PRE>

Tempdb 위치 변경하기

[따라하기] Tempdb를 디스크 상의 다른 위치로 이전하기
아래 예제는 tempdb에만 적용할 수 있으며, 사용자 데이터베이스를 이동하고자 하는 경우에는 sp_detach_db와 sp_attach_db를 사용하기 바랍니다. 이에 대한 자세한 내용은 [데이터베이스 이전하기]를 참조하십시오.

1. tempdb 데이터베이스의 논리 파일 이름을 확인합니다. <PRE>USE tempdbGOEXEC sp_helpfileGO/* 결과tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf PRIMARY 102400 KB Unlimited 10% data onlytemplog 2 C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf NULL 20480 KB Unlimited 5120 KB log only*/</PRE>
2. ALTER DATABASE 명령어를 사용하여 파일의 위치를 변경합니다. <PRE>USE masterGOALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\DBData\tempdb.mdf')GOALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\DBData\templog.ldf')GO</PRE>
3. SQL Server 를 중지한 후 다시 시작합니다.
4. SQL Server 서비스가 시작된 다음에, 다음의 확인작업을 수행합니다. <PRE>USE tempdbGOEXEC sp_helpfileGO</PRE>
5. 기존의 tempdb 파일들을 삭제합니다.

데이터베이스 파일 변경하기

• 파일 크기 확장하기
  [따라하기] Sample 데이터베이스 sample_dat 파일을 200MB로 확장하기. <PRE>ALTER DATABASE sampleMODIFY FILE(NAME = sample_dat,SIZE = 200MB)GO</PRE>
• 파일 증가 규칙 변경하기
  [따라하기] Sample 데이터베이스 sample_dat 파일의 증가율을 5MB로 변경하기. <PRE>ALTER DATABASE sampleMODIFY FILE(NAME = sample_dat,FILEGROWTH = 5MB)GO</PRE>
• 새로운 파일 그룹 추가하기
  [따라하기] 파일 그룹 추가하기
  Sample 데이터베이스에 sample_Fg 파일 그룹을 추가한 후, 그 파일그룹에 Sample_New 파일을 추가합니다. 주 데이터 파일이 아닌 데이터 파일의 확장자는 .ndf입니다. <PRE>ALTER DATABASE SampleADD FILEGROUP Sample_FgGOALTER DATABASE SampleADD FILE(NAME = Sample_New,FILENAME = 'c:\data\Sample_New.ndf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 3MB)TO FILEGROUP Sample_FgGO</PRE>

데이터베이스 축소하기

데이터베이스의 IsAutoShrink 옵션을 true로 설정하여, 데이터베이스를 축소하는 방법도 있으나, DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE를 사용하여 수동으로 데이터베이스를 축소하는 방법이 있습니다. 관련 데이터베이스의 특정 데이터 파일이나, 트랜잭션 로그 파일을 축소하는 경우에는 DBCC SHRINKFILE을 사용합니다.

• 파일 지정 없이 축소하기
  [따라하기] 파일 지정 없이 Sample 데이터베이스 전체 크기 중에서 10%의 여유공간이 남도록 파일 크기를 축소합니다. <PRE>DBCC SHRINKDATABASE (Sample, 10)GO</PRE>
• 특정 파일 축소하기
  [따라하기] Sample 데이터베이스의 sample_dat 파일을 10MB로 축소합니다. <PRE>USE SampleGODBCC SHRINKFILE (sample_dat, 10)GO</PRE>
• 가상 로그 파일 축소하기
  

  트랜잭션 로그 파일이 여러 번 자동 증가가 발생한 경우, 여러 개의 가상 로그 파일들로 조각화되어, 로그 관련 작업의 성능에 좋지 않은 영향을 미칩니다. 가상 로그 파일이 25개 이상일 경우, 가상 로그 파일을 제거하고, 트랜잭션 로그 파일을 적절한 크기로 변경합니다.

  [따라하기] Sample 데이터베이스의 Sample_log 로그 파일의 가상 로그 파일을 제거하여, 트랜잭션 로그 파일을 축소합니다.

  1. 1. 가상 로그 파일 확인합니다. 결과 행의 수가 가상 로그 파일의 수입니다. <PRE>USE SampleGODBCC LOGINFOGO</PRE>
  2. 2. 트랜잭션 로그 백업을 수행합니다. 로그 백업을 받을 수 없는 경우에는 로그를 삭제합니다. <PRE>BACKUP LOG Sample TO DISK='D:\DBBackup\Sample_Log.bak'GO-- 또는 BACKUP LOG Sample WITH NO_LOGGO</PRE>
  3. 3. 트랜잭션 로그 파일의 크기를 가능한 한 작은 크기로 축소합니다. <PRE>EXEC sp_helpfileGODBCC SHRINKFILE (Sample_log, TRUNCATEONLY)GO</PRE>
  4. 4. 로그 파일의 크기를 적절하게 변경합니다. <PRE>ALTER DATABASE SampleMODIFY FILE ( NAME = 'Sample_log' , SIZE = 30)GO</PRE>

데이터베이스 옵션 설정하기

[따라하기] 데이터베이스 옵션 확인하기
데이터베이스 옵션의 현재 설정이나 지정된 데이터베이스의 속성에 대한 정보의 확인은 SQL Server 2000에서 제공하는 함수인 DATABASEPROPERTYEX를 사용합니다.

<PRE>SELECT DATABASEPROPERTYEX ('pubs', 'IsAutoUpdateStatistics')GO</PRE>

[참고]
새로운 데이터베이스를 만들 때 FOR ATTACH가 지정된 경우를 제외하면 model 데이터베이스의 데이터베이스 옵션 설정을 상속받습니다. 예를 들어, 데이터베이스 옵션 select into/bulkcopy는 model 데이터베이스와 모든 새 데이터베이스에서 OFF로 설정됩니다. 그러므로 ALTER DATABASE를 사용하여 model 데이터베이스의 옵션을 변경하면 변경된 옵션 설정이 이후에 새로 만들어지는 모든 데이터베이스에 적용됩니다.

<PRE>/* model 데이터베이스의 옵션 설정 */USE modelGOALTER DATABASE model SET AUTO_UPDATE_STATISTICS OFF, RECOVERY BULK_LOGGEDGO/* model 데이터베이스의 변경된 옵션정보 확인 */SELECT DATABASEPROPERTYEX ('model', 'IsAutoUpdateStatistics')SELECT DATABASEPROPERTYEX ('model', 'Recovery')GO</PRE>

데이터베이스 소유자 변경하기

<PRE>USE SampleEXEC sp_changedbowner 'dbadmin'GO</PRE>

데이터베이스 이름 변경하기

<PRE>EXEC sp_renamedb Sample, Sample_RenameGO</PRE>

백업과 복구

모든 데이터베이스 운영 환경은 반드시 장애 복구에 대한 백업과 복구 계획을 가지고 있어야 합니다. 백업과 복구 계획은 실제 운영 서버를 백업하여 실제와 동일한 상태로 철저히 테스트하고 문서화해야 합니다. 백업과 복구 계획은 응용 프로그램과 운영 체제의 구성 요소를 포함하여, 전체 시스템에 대하여 문서화해야 하며, 발생 가능한 모든 장애 시나리오를 고려하여 문서화해야 합니다. 반드시 규칙적인 테스트를 수행해야 합니다. 계획을 수립할 때에는, 시스템이 얼마동안 다운되어도 무방한지, 어느 정도의 데이터가 유실되어도 되는지에 관련된 리소스 비용과 다운타임, 복구 비용을 고려합니다.

복구 모델

복구 모델은 트랜잭션 로그에 어떤 내용이 저장되었는지를 가리키는 것입니다. SQL Server Standard Edition, Enterprise Edition의 디폴트 복구 모델은 전체 백업 모드입니다. 디폴트 복구 모델은 데이터베이스가 만들어질 때 model 데이터베이스에 설정된 값에 의하여 결정됩니다.

• 단순 복구 (Simple)
  - 마지막으로 백업을 시행한 시점까지의 백업된 정보를 복구합니다.
  - 전체 백업과 차등 백업을 이용할 수 있으나, 트랜잭션 로그 백업은 할 수 없습니다.
  - 이 모델은 응용 프로그램을 테스트하는 테스트 환경 또는 저장된 데이터를 복구할 필요가 전혀 없는 시스템에 적합합니다.
  
• 전체 복구 (Full)
  - 모든 변경 사항이 트랜잭션 로그에 기록됩니다.
  - 문제가 발생한 시점이나 과거의 백업을 받은 특정한 시점까지의 정보를 복구할 수 있습니다.
  - 전체 백업, 차등 백업, 트랜잭션 로그 백업을 모두 이용할 수 있습니다.
  
• 대량 로그 복구 (Bulk_Logged)
  - 대량 작업이나 대량 로딩에 대한 기록은 최소화하기 때문에, 백업 전에 발생한 대량 작업의 오류는 수작업으로 보정해야 합니다.
  - 전체 백업, 차등 백업, 트랜잭션 로그 백업을 모두 이용할 수 있습니다.
  

[따라하기] 데이터베이스 복구 모델 변경하기

<PRE>ALTER DATABASE SampleSET RECOVERY BULK_LOGGEDGOALTER DATABASE TestDBSET RECOVERY SIMPLEGOALTER DATABASE TestDBSET RECOVERY FULLGO</PRE>

[참고] 복구 모델 전환 시 백업 전략

변경 전	변경 후	작업	설명
전체 복구	대량 복구	없음	백업 전략의 변화는 없다.
전체 복구	단순 복구	변경하기 전에 선택적으로 트랜잭션 로그를 백업한다.	변경 시점까지 복원을 위해 변경 전에 로그 백업을 한다. 단순 복구 모델로 전환한 후에는, 로그 백업을 중지한다.
대량 복구	전체 복구	없음	백업 전략의 변화는 없다.
대량 복구	단순 복구	변경하기 전에 트랜잭션 로그를 선택적으로 백업한다.	변경 작업 전에 로그 백업을 하는 것으로 특정 시점까지 복원하는 것이 가능하다. 단순 복구 모델로 변경 후에는 로그 백업을 중지한다.
단순 복구	전체 복구	변경 후에 데이터베이스 백업을 수행한다.	전체 복구 모델로 전환된 후 전체 데이터베이스 백업 또는 차등 백업을 수행한다. 주기적으로 데이터베이스 백업, 로그 백업, (선택적으로) 차등 백업을 수행한다.
단순 복구	대량 복구	변경 후에 데이터베이스 백업을 한다.	대량 복구 모델로 전환된 후 전체 데이터베이스 백업 또는 차등 백업을 수행한다. 주기적으로 데이터베이스 백업, 로그 백업, (선택적으로) 차등 백업을 수행한다.

백업의 종류

• 전체 백업
  데이터베이스를 구성하는 모든 데이터 파일들을 백업합니다. 시스템과 사용자 정의 데이터베이스에서 주기적으로 수행되어야 합니다.
• 파일 또는 파일 그룹 백업
  파일 그룹을 구성하는 파일들 중에서 하나의 파일이나 여러 개의 파일들을 백업합니다. 전체 백업보다 훨씬 빠르고, 업무 단위의 백업이 가능하여, 대량의 데이터베이스일 경우에 효율적이기는 하지만, 백업받은 데이터만 보호된다는 단점이 있습니다.
• 차등 백업
  마지막 전체 백업이 실행된 이후 변경된 정보를 백업합니다. 즉, 두 번째 차등 백업은 첫 번째 차등 백업과 중복되는 부분이 있으므로, 복원 시에는 전체 백업과 장애가 발생하기 전의 마지막 차등 백업을 복원하면 됩니다. 차등 백업 전략을 사용하면 복구 속도를 향상시킬 수 있습니다.
• 트랜잭션 로그 백업
  트랜잭션 로그 백업은 전체 복구 또는 대량 로그 복구 옵션으로 설정된 데이터베이스에서만 사용 가능하며, 이 경우 데이터베이스에 변경이 발생할 때마다 그 변경에 대한 모든 정보가 트랜잭션 로그에 기록됩니다. 트랜잭션 로그는 연속적으로 변경 내역을 저장합니다. 복원할 경우에는, 마지막 전체 백업을 실행한 시점부터 순차적으로 실행한 모든 트랜잭션 로그 백업이 필요합니다. 다시 말씀드리지만 복구 모델이 "단순 복구"일 경우에는, 트랜잭션 로그 백업은 사용할 수 없습니다.

백업 전략 세우기

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

번호	수칙	체크
1	시스템 데이터베이스도 백업을 수행합니다.
2	백업 전략은 복구 시간까지 감안하여 계획을 세웁니다.
3	트랜잭션 로그를 정기적으로 백업하지 않는다면, 정기적으로 비워 줍니다.
4	백업 파일은 데이터베이스 파일이 저장된 디스크와 물리적으로 다른 디스크에 저장합니다.
5	주기적으로 백업 파일이 제대로 복원되는지 테스트합니다.

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

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

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

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

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

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

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

<PRE>BACKUP LOG Sample WITH NO_LOG -- 또는 BACKUP LOG Sample WITH TRUNCATE_ONLY</PRE>

[참고] 데이터베이스가 단순 복구 모델이거나 "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