찐빵의 IT World

작업을 하다보면 DB를 백업해야 하는 경우가 있습니다. 

보통 EXPORT를 사용하는 것 같은데 종종 에러도 생기고 해서 좀 더 쉬운 방법을 찾아봤습니다.

여기서는 스크립트를 제작해서 간단하게 추출, 주입하는 방법을 소개합니다.

MSSQL 2008을 기준으로 설명하겠습니다.

1. 일단 MSSQL을 킵니다.

이 글은 오라클 기반의 글 입니다.

 1. RBO (Rule Based Optimizer)

머지 않아 소멸될 것이므로 간단한 개념만 소개한다.

RBO는 인덱스 구조나 비교연산자에 따라 순위를 부여하여 이것을 기준으로 최적의 경로를 결정

순위

① ROWID로 1 로우 액세스

② 클러스터 조인에 의한 1 로우 액세스

③ Unique HASH Cluster에 의한 1 로우 액세스

④ Unique INDEX에 의한 1 로우 액세스

⑤ CLUSTER 조인

⑥ Non Unique HASH Cluster Key

⑦ Non Unique Cluster Key

⑧ Non Unique 결합 인덱스

⑨ Non Unique한 컬럼 인덱스

⑩ 인덱스에 의한 범위 처리

⑪ 인덱스에 의한 전체범위처리

⑫ Sort Merge 조인

⑬ 인덱스 컬럼의 MIN, MAX 처리

⑭ 인덱스 컬럼의 ORDER BY

⑮ 전체테이블 스캔

RBO 의 단점

통계정보 무시

RBO 의 장점

옵티마이저의 판단이 매우 규칙적이고 분명하며 사용자가 정확히 예측할 수 있다.

전략적인 인덱스를 구성할 수만 있다면 이 규칙의 보편 타당성이 매우 높다.

 2. CBO(Cost Based Optimizer)

미리 작성해둔 다양한 통계정보를 참조한다. 통계정보에는 테이블의 로우 수와 블록 수, 블록 당 평균 로우 수, 로우의 평균길이, 컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL 값의 수, 클러스터링 팩터, 인덱스의 깊이, 최대 최소값, 리프 블록 수, 가동 시스템의 I/O나 CPU/ 사용정보 등 많은 정보 보유

CBO 의 장점

- 최대의 장점은 현실을 감안한 판단을 할 수 있다(통계정보 기반)

- 통계정보의 관리를 통해 최적화를 제어 할 수 있다

- 옵티마이져를 깊이 이해하고 있지 않더라도 최소한의 성능이 보장된다

쓰다가 일이 생겨서... 추후 업데이트!

-MSSQL

MSSQL은 NULL값도 Index를 잡는다. 그래서 만든 것이 필터된 인덱스 인데 문제가 많다.

-ORACLE

ORACLE은 NULL값은 Index를 잡지 않는다.

 OLTP에서 B-Tree 인덱스를 쓰는 이유

OLTP(Online Transaction Processing) 시스템에서 B-Tree 인덱스를 사용한다. B는 'Balaced'를 말하기도 하고 'Binary'를 말하기도 한다. 하지만 내가 본 책(새로쓴대용량데이터베이스솔루션Vol.1 이화식 지음) 에서는 Balaced에 더 중점을 두고 있다. 잎사귀에 이르는 깊이가 어떤 인덱스 로우에 대해서도 동일하기 때문이다.

테이블의 로우가 어떤 위치에 있든 동일한 처리방법과 속도로 접근할 수 있다는 것이 인덱스가 가지는 가장 큰 특징이다. Leaf수준으로 갈 수록 가지가 늘어나는 수는 제곱이 되기 때문에 아무리 규모가 커져도 가지 깊이가 약간 늘어나는 것만으로도 천문학적인 수의 Leaf수준을 관리할 수 있다는 뜻이다.

 UPDATE

- 조건에 맞는 데이터 변경

UPDATE dbo.Employee
    SET EmpName = N'홍길퉁'
    WHERE EmpID = 'S0001'
GO

- FROM 절을 사용한 조건 지정

UPDATE dbo.Employee
    SET Salary = Salary *0.8
    FROM dbo.Employee e1
    WHERE (SELECT COUNT(*)
        FROM dbo.Vacation
        WHERE EmpID = e1.EmpID) > 2
GO

- 기본적인 INSERT 문

INSERT INTO dbo.Department(DeptID, DeptName, UnitID, StartDate) 
VALUES('PRD', N'상품', 'A', GETDATE()) 
GO

- 열 정보 생략 하여 INSERT 하기

IDENTITY 속성의 열을 제외한 모든 열이거나 나열될 열이 테이블에 포함된 모든열 일때

INSERT INTO dbo.Department 
VALUES('DBA', N'DB관리', 'A', GETDATE())

- 동시에 여러 행 INSERT

INSERT INTO dbo.Department 
VALUES('OPR', N'운영', 'A', GETDATE()), ('CST', N'고객서비스', NULL, GETDATE()) 
GO

- 상위 n개 INSERT

- Vacation과 같은 구조의 SampleVacation 테이블 만들기

SELECT * 
INTO dbo.SampleVacation 
FROM dbo.Vacation 
WHERE 1 = 0 
GO

- Vacation테이블의 상위 5 건만 SampleVacation 테이블에 입력

INSERT TOP(5) 
    INTO dbo.SampleVacation 
    SELECT EmpID, BeginDate, EndDate, Reason, Duration 
        FROM dbo.Vacation 
            ORDER BY BeginDate DESC 
GO

- 저장 프로시저 결과 INSERT

-- 저장 프로시저 만들기
CREATE PROC dbo.usp_GetVacation
        @EmpID char(5)
AS
        SELECT EmpID, BeginDate, EndDate, Duration
            FROM dbo.Vacation
            WHERE EmpID = @EmpID
GO

-- 임시 테이블 만들기
CREATE TABLE #Vacation (
    EmpID char(5),
    BeginDate datetime,
    EndDate datetime,
    Duration int
)
GO

-- 저장 프로시저 결과 INSERT
INSERT INTO #VACATION EXEC dbo.usp_GetVacation 'S0001'
GO

SELECT * FROM #Vacation
GO

- IDENTITY 속성에 INSERT 하기

IDENTITY가 2번인 행을 지웠다고 가정하자.

IDENTITY가 2번인 행을 INSERT 하기 위해서 해야 할 일을 알아보자.

-- 임의의 IDENTITY값 INSERT 하기
SET IDENTITY_INSERT dbo.Vacation ON
GO

INSERT INTO dbo.Vacation(VacationID, EmpID, BeginDate, EndDate, Reason) -- 모든 열 정보를 다 나열해야 한다
VALUES(2, 'S0003', '2007-01-22', '2007-01-08', N'신년 맞이 기분 내기')
GO

SET IDENTITY_INSERT dbo.Vacation OFF
GO

SELECT * FROM dbo.Vacation
GO

 SELECT

- 모든 데이터 가져오기

SELECT * FROM dbo.Employee

- 특정 열의 데이터 가져오기

SELECT EmpID, HireDate, Email 
FROM dbo.Employee 
GO

-특정 행의 데이터만 가져오기

SELECT * 
FROM dbo.Employee 
WHERE EmpID = 'S0005' 
GO

- 특정 열의 특정 행만 가져오기

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE EmpID = 'S0005' 
GO

- 비교 연산자 사용

SELECT EmpID, EmpName, HireDate, EMail
FROM dbo.Employee 
WHERE Salary >= 8000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate < '2007-01-01'

- 문자열 비교

SELECT EmpID, EmpName, HireDate, Email 
FROM dbo.Employee 
WHERE EmpName = '홍길동'

SELECT EmpID, EmpName, HireDate, Email 
FROM dbo.Employee 
WHERE EmpName LIKE '김%'

- 논리 연산자 사용

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate >= '2008-01-01' AND Salary >= 6000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate >= '2008-01-01' OR Salary >= 6000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE HireDate >= '2008-01-01' AND NOT Salary >= 6000

- 범위 조건 지정

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE Salary BETWEEN 6000 AND 8000

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE Salary >=6000 AND Salary <= 8000

- 리스트 조건 지정

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE DeptID IN ('SYS', 'MKT', 'HRD')

SELECT EmpID, EmpName, HireDate, EMail 
FROM dbo.Employee 
WHERE DeptID = 'SYS' OR DeptID = 'MKT' OR DeptID = 'HRD'

① SELECT, INSERT, UPDATE, DELETE 한꺼번에 주는 법(1개의 데이터베이스에 해당 스키마)

데이터베이스 - 해당DB - 보안 - 사용자 - 원하는 사용자 더블클릭 - 일반탭에서 소유한 스키마에 원하는 스키마 클릭

이렇게 하면 해당 사용자가 해당 스키마로 만들어진 테이블들에 대한 권한을 가진다.

스키마 소유권 주기(스키마 소유권은 1스키마당 1사용자)

USE 해당DB
GO
ALTER AUTHORIZAION ON SCHEMA::[해당스키마] TO [사용자]
GO

② 한 스키마에 대해서 원하는 권한 선택하여 넣어주기

데이터베이스 - 해당DB - 보안 - 스키마 - 원하는 스키마 더블클릭 - 사용권한탭에서 사용자 검색하여 추가 - 아래에 원하는 권한 선택하여 주기

USE AdventureWorks2008
GO

GRANT SELECT ON SCHEMA::HumanResources TO UserA
GO

③ 추후 업데이트

 배치작업, 스케쥴, 에이전트 작업 스크립트로 빼서 옮기기

① 한개만 옮기기

② 여러 개 한꺼번에 옮기기

개체 탐색기 정보는 보기 에서 누르거나 F7을 누르면 된다.

1. 쿼리로 현재 구성된 모든 테이블에 select 를 주는 방법

2. db_datareader 권한을 부여하는 방법

두가지 권한의 차이는... 

1번으로 수행한 경우에는.. 수행 후에 디비에 테이블이 추가가 되면.. 추가된 테이블에 대해서는 권한을 주지 않았기 때문에 select가 불가능해집니다.

1번 방법: 현재 있는 모든 테이블 SELECT 권한 주는 방법

exec sp_msforeachtable 'GRANT select on ? to test'

2번 방법:

exec sp_addrolemember 'db_datareader','test'

2번 방법은 추가되는 테이블에도 계속 적용이 되기 때문에 보다 효율적

추가 : SELECT 된 모든 테이블의 권한을 뺐기

exec sp_msforeachtable 'REVOKE select on ? to test'

sp_msforeachtable SP에 관련된 라이고님의 좋은 글: http://laigo.kr/307