MS-SQL 복원

복원

  • 백업한 데이터를 복구하는 것을 말한다.

쿼리

  • Restore Database DB이름 From Disk=’경로\파일명.bak’

복원시 옵션

  1. 일반 옵션
    • File : 한 파일 내에 여러개의 백업 존재 시, 백업 디바이스 헤더 검사에서의 Position 값을 넘겨주면 해당 백업만 복원합니다.

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With File = 3 – 해당 파일의 3번 Position의 백업만 복원

    • DBO_ONLY : 복원 이후 권한을 db_owner 에게만 준다

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With Dbo_Only

  2. 복구 이후 상태 설정용 옵션
    • Recovery : 복원이 끝났으니 사용하게 하라는 의미.

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With Recovery – 복원을 끝내고 사용할 수 있도록 설정

    • NoRecovery : 추가적인 복구 작업이 있을 것이기에 사용하게 하지 말라는 의미.

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With Recovery – 복원을 끝내도 사용은 할 수 없게 설정

    • Standby : 추가적인 복구가 있을 예정이지만, 사용은 하고 싶기에 읽기 전용 상태로 설정.

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With Standby – 복원을 끝내고 읽기 전용으로 설정

  3. 범위 지정 복구 관련 옵션
    • StopAt : 복원 시점을 시간으로 지정

      Restore Database DB이름 From Disk=’경로\파일명.bak’ StopAt = ‘2001-11-19 01:09’ –2001년 11월 19일 1시 9분 데이터까지 복원

  4. 마크 이용 복구 관련 옵션
    • 트랜잭션에 마크 설정

      Begin Tran myTran With Mark –트랜잭션에 마크를 설정 – 여러가지 처리 Commit

    • StopAtMark : 지정한 마크의 트랜잭션까지 복원

      Restore Database DB이름 From Disk=’경로\파일명.bak’ StopAtMark = ‘myTran’ – myTran 이라는 Transaction 까지 복원

    • StopBeforeMark : 해당 트랜잭션 직전(해당 트랜잭션을 포함하지 않음.) 에서 복원을 중지한다.

      Restore Database DB이름 From Disk=’경로\파일명.bak’ StopBeforeMark = ‘myTran’ –myTran 이전 까지의 Transaction 까지 복원

    • After : 날짜 지정 기능 (StopAtMark나, StopAtBeforeMark 과 함께 사용 가능)

      Restore Database DB이름 From Disk=’경로\파일명.bak’ StopAtMark = ‘myTran’ AFTER ‘2001-11-19 01:09’ –2001년 11월 19일 1시 9분 데이터까지 복원

  5. 현재 상태를 파일로 저장시 옵션
    • MOVE A TO B : 복원시 현재 상태를 파일로 저장할 파일 경로 재정의 명령

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With Move ‘논리 파일 이름’ To ‘경로\파일명.mdf’ – 해당 논리 파일을 지정한 곳으로 옮겨라

    • Replace : 복원시 현재 상태를 저장할 때 같은 파일 존재시 덮어 써라. (Move 명령과 함께 옵션 지정 가능)

      Restore Database DB이름 From Disk=’경로\파일명.bak’ With Replace– 파일명.bak이 이미 있을 경우 덮어 쓰게 설정

복구 모델

  1. 전체 복구 모델 (FULL)
    • 데이터 백업과 로그 백업을 사용하여, 손실 없이 복구하는 모델.
    • 시간 지정 복구가 가능하다.
    • 전체, 차등, 로그, 파일 백업을 할 수 있음.
  2. 대량 로그 복구 모델 (Bulk_Logged)
    • 최소의 로그와 데이터 페이지를 저장하고 복구하는 모델.
    • 지정된 시점까지의 복구(Stopat 옵션)는 허락 안된다.
    • 전체, 차등, 로그, 파일 백업을 할 수 있음.
  3. 단순 모델 (Simple)
    • 로그를 가장 적게 사용한다. trunc.log on chkpt. 데이터 베이스 옵션과 동일.
    • 로그를 주기적으로 비우기때문에 로그가 가득 차지 않는다.
    • 지정된 시점까지의 복구(Stopat 옵션)는 허락 안된다.
    • 전체, 차등 백업만 할 수 있음.

MS-SQL 조인 내부 처리 방식 정리

참고 자료

용어 정리

  • Driving Table : 조인에서 기준이 되는 테이블 (= Outer Table)
  • Drived Table : 조인에서 결합 되어지는 테이블 (= Inner Table)

Nested Loop

  • 두개 이상의 테이블에서, Driving Table에서 먼저 조건에 맞는 집합을 만들고, Drived Table에서 조건이 맞는 결과를 얻어낸 후 Row를 결합하여 원하는 결과를 추출하는 방식 (Driving Table의 한 행당 Drived Table의 데이터 검색이 일어난다).
  • 부분 범위 처리 가능 (정지, 재개 가능. 예를 들어 조건에 만족하는 데이터 1000개만 얻어낸 상태에서, 요청이 들어올경우 1001~2000번째 값을 얻어오는 것이 가능)
  • Driving Table에서 상대 테이블로 비교할 데이터 량이 적은 것이 효율에 좋다.
  • 기준 테이블과 상대 테이블 간에 서로 대응하는 값에 인덱스가 걸려있는 것이 효율에 좋다.
  • 특정 범위만 알아오는 쿼리에 유용.
  • Any Join에서 사용 됨.

Sort Merge

  • 쿼리에 따라 기준 테이블, 상대 테이블 각각 조인에 포함될 데이터들만 추려내 테이블로 구성한 후 (집합을 구성), 추린 테이블의 데이터를 정렬해서 사용한다. (상황에 따라 테이블 전체를 정렬해서 사용하는 경우도 있다.)
  • 두 테이블의 크기가 다를 경우, 유후 시간 발생 (정렬 되는 시간이 동일하지 않기 때문에)
  • 지그 재그 계단식 비교 방식을 취함 (읽고 있는 데이터 포인터를 기억한 후, 다음 데이터를 읽을 때 이미 읽은 위치를 다시 읽지 않는 방식으로 검색량을 줄인다.)
  • 지그 재그 계단식 비교를 하기에, 겹치는 값이 존재할 경우 이미 읽은 데이터를 다시 읽어야 되는 상황이 발생하기도 한다. (같은 값이 여러개일 경우, 방금 읽었던 값도 또 읽어야 하기에) 그래서, 겹치는 값이 존재할 경우 DBMS가 Hash Join을 사용할 가능성이 높다.
  • 추려진 결과가 한번에 나오고, 그 전에는 결과를 낼 수 없기에, 처리 속도가 중요한 쿼리에서는 사용되지 않는다.
  • Any Join 에서 가능 하지만 주로 Equal Join 에서 유용.

Hash

  • 테이블 내의 모든 데이터에 해시 함수를 적용하여 해시 버킷에 담는다. 값 별로 버킷에 나누어 담기에, 해당 버킷을 읽음으로써 같은 값을 사용하는 집합에 억세스 하는 방식이다.
  • 해시 테이블 구성 비용 (CPU, 메모리)이 크다.
  • 값 별로 버킷에 담기기에 버킷 억세스는 같은 값을 찾는 경우에만 빠르다. 그래서, Equal Join 에서만 가능하도록 되어있다.

MS-SQL 데이터 저장 방식

Page

MS-SQL에서 다뤄지는 데이터가 저장되는 최소 단위는 Page 입니다. Page의 크기는 8KB ( 1024 * 8 = 8192 btyes ) 이지만, 실제로 데이터를 저장할 수 있는 maximum row size 는 8090 bytes 입니다.

Page의 구성

  1. header (96 bytes)
    • 이전페이지와 다음페이지의 정보 저장
  2. Data rows (8090 bytes)
    • 실제 데이터 저장부분
  3. offset 부분
    • 각 행의 첫번째 바이트가 페이지의 시작부분에서 얼마나 멀리 떨어져 있는지를 저장

Extent

페이지가 최대 8개가 모여 한개의 Extent를 이룹니다. 테이블, 인덱스가 물리적으로 저장되는 최소 단위라고 생각하시면 됩니다. Extent의 크기는 64KB ( 8192 * 8 = 65536 bytes ) 입니다. 1MB당 16개의 extent 가 존재할 수 있다.

Extent의 분류

  1. single extent (단일)
    • 테이블면 테이블, 인덱스면 인덱스 하나의 단일한 데이터가 저장된 상태
  2. mix extent (혼합)
    • 테이블과 인덱스등의 하나이상의 데이터베이스의 오브젝트들이 같이 저장된 상태

MS-SQL 백업

백업시 참고 사항

  • 기본적으로 model, northwind, Pubs, tempdb는 백업하지 않아도 됨.
  • master테이블은 권한과 같은 정보들이 있고, msdb테이블은 스케쥴이나 패키지 작업이 저장되어 있습니다. 필요한 데이터일 경우에만 백업하면 됩니다.

전체 백업

  • 전체 백업은 데이터의 변경 유무에 관여하지 않고 전체 데이터의 복사본을 만드는 백업 방식입니다.
  • 전체 백업은 복구 과정이 다른 백업 방식보다 간편하고 다른 백업 방식보다 복구 시간이 적게 소요됩니다.

쿼리

Backup Database DB이름 To Disk=’경로\파일명.bak’

차등 백업

  • 차등 백업은 마지막 전체 백업 이후 변경된 모든 데이터를 백업하는 방식입니다. 이는 증분 백업과는 다르게 전체 백업 이후 파일이 변경될 경우 다음 전체 백업까지 계속 백업하는 방식입니다.
  • 전체 백업 이미지와 가장 최근의 차등 이미지만 복구하면 되기 때문에 복구 시점에 따라 다르긴 하지만 대개 증분 백업보다 복구 속도가 빠릅니다.
  • 파일이 변경될 때마다 파일 크기가 증가하게 되며, 다음 전체 백업 때까지 파일 크기가 점점 커지게 된다는 단점이 있습니다.

쿼리

Backup Database DB이름 To Disk=’경로\파일명.bak’ With Differential

로그 백업

  • 로그 백업은, 데이터베이스를 백업 받는게 아니고 그동안 발생했던 트랜잭션 로그(지금까지 이루어졌던 모든 동작들의 로그)를 백업 받는 방법입니다.
  • 전체 백업시에도 로그 백업은 삭제 되지 않습니다. 그렇기 때문에 전체 백업을 하기 전에 로그 백업을 모두 삭제하고 전체 백업을 하는 것이 좋습니다.
  • 로그 백업은 시간 단위로 복원이 가능하다는 장점이있습니다. (부분 롤백 가능)
  • 트랜잭션 로그 백업을 받으면 백업받은 로그가 제거되어 로그 파일의 사이즈를 줄여 줄 수 있게 됩니다. (증분 백업의 개념으로 사용 가능)

쿼리

Backup Log DB이름 To Disk = ‘경로\파일명.bak’ GO

백업시 옵션

  • Init (Overwrite) : 덮어 쓰기 모드

    Backup Log DB이름 To Disk = ‘경로\파일명.bak’ With Init

  • NoInit (Append) : 뒤에 이어 붙이는 모드. (Default)

    Backup Log DB이름 To Disk = ‘경로\파일명.bak’ With NoInit

  • No_Truncate : 로그 백업을 하고나서, 로그를 지우지 말라는 뜻. (데이터 베이스가 비정상적인 상태에서도 가능)

    Backup Log DB이름 To Disk = ‘경로\파일명.bak’ With No_Truncate GO – 로그 백업후 로그를 지우지 말라는 의미.

백업 받은 파일 검사

한 파일에 여러번 백업할 수도 있고, 한 파일당 한번 백업할 수도 있다. 백업 받은 파일에 백업이 정상적으로 이루어졌는지 등의 정보를 확인하려면 아래와 같은 쿼리로 확인하면 된다.

  • 백업 디바이스 헤더 검사

    Restore HeaderOnly From Disk = ‘경로\파일명.bak’ GO

  • 백업시 파일 리스트 검사

    Restore FileListOnly From Disk = ‘경로\파일명.bak’ GO

  • 백업 미디어 정보 검사

    Restore LabelOnly From Disk = ‘경로\파일명.bak’ GO

  • 백업 안정성 검사

    Restore VerifyOnly From Disk = ‘경로\파일명.bak’ GO

인덱스가 있지만 인덱스를 안 타는 경우

  1. 인덱스 컬럼의 변형 select * from table where LOWER(name) =’word’; select * from table where idx - 1 = 5; 이 처럼 인덱스에 변형을 가하게 되면, DBMS가 인덱스를 이용하지 않는다.
  2. NOT 또는 IN 연산자 사용 NOT일 경우 무조건 인덱스를 안타는 것이 아니다. NOT일 경우에도 인덱스를 타긴 타지만, 일반적으로, NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다. 마찬가지로 IN일 경우에도, IN에 포함된 데이터들의 비율이 매우 높다면 FULL SCAN을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다.
  3. 와일드 카드 LIKE문장에서 범위를 전체를 지정시 select * from table where name like ‘%word’; 문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 FULL SCAN이 이루어진다.

    select * from table where name like ‘word%’; 당연한 얘기지만 쿼리가 이런 경우 인덱스를 탄다. 문자열 정렬 순서를 그대로 이용할 수 있기 때문이다.

  4. 복합 컬럼 index에서 조건이 잘못되여 index 가 적용 되지 못하는경우 select * from table where name = ‘word’ or idx = 5 name과 idx가 둘다 인덱스가 걸려있는 경우라해도, DBMS가 최적의 OR 조건을 뽑기 힘들어, FULL SCAN 하는 경우가 많다.
  5. Optimizer 의 선택 select * from table where name =’word’ and id =’elky’; 인덱스가 name 과 id로 2개가 있을 경우 id나 name 인덱스 중 하나가 선택될수도 있고, 둘다 선택 될 수도있다. 어떤 방식으로 선택하는냐가 속도에 중요할수도있다. 즉 실행 계획을 추적해서 원하는 결과가 나오도록 관리가 필요하다