개발자(開發者) a developer

출처:http://www.sqlworld.pe.kr/index.asp

저작권 문제시 즉시 삭제가능~!!!

===========================================================================

백업장치

===========================================================================

1. 백업 장치? 돈주고 사나요?

백업 장치를 만든다는 것은 어떤 하드웨어적인 장치를 준비하는 것을 이야기 하는 것이 아닙니다. 여기서 이야기 하는 백업 장치는 물리적인 파일 이름이 길고 복잡하기 때문에 이를 쉽게 사용하기 위해서 하나의 별칭(Alias)를 만들어 두는 것을 이야기 합니다. 기억하실지 모르겠지만 데이터베이스를 만들 때 물리적인 파일의 경로와 이름을 지정하는 것 이외에 논리적인 데이터베이스 파일명을 지정하게 됩니다. (기억이 안난다면 데이터베이스 관련 강좌를 한번 보고 오시기 바랍니다).

2. 백업 장치의 필요성

예를 들어 "C:\Program FIles\Microsoft SQL Server\MSSQL\Backup\sqlworld.bak" 이라는 이름의 물리적인 파일로 백업을 받는다고 했을 경우 너무나 긴 경로명 때문에 왠지 짜증이 납니다. 이를 해결해 주는 것이 바로 백업장치명, 즉 논리적인 파일명이 됩니다.

나중에 보겠지만 다음과 같은 백업을 받은 T-SQL 문이 있가도 가정하겠습니다.

BACKUP DATABASE sqlworld TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL\Backup\sqlworld.bak'

위에서 빨간색 부분의 파일 경로는 너무나 길고 짜증납니다. 하지만 앞으로 배우게 될 방법으로 백업장치인 sqlworld를 만들어 놓게 되면 위 백업 문장은 다음과 같이 바뀌게 됩니다.

BACKUP DATABASE sqlworld TOsqlworld

매우 간단한 백업 문장이 되었습니다. 이것이 백업 장치의 위력(?) 입니다.

3. 백업 장치 만들기

백업 장치를 만드는 방법은 두가지가 있습니다. 추측하시다 시피 EM(Enterprise Manager)를 이용한 방법이 있고 T-SQL문을 이용하는 방법이 있습니다. 이 두가지 방법에 대하여 자세히 살펴보도록 하겠습니다.

[참고] 대부분의 작업들이 EM을 이용해서도 가능하고 T-SQL문을 이용해서도 가능합니다. 이중에 어느것을 사용하는게 좋은지 묻는 경우가 있습니다. EM의 경우는 대화 화면을 보면서 작업을 하기 때문에 더 쉽게 작업을 할 수 있습니다. 하지만 했던 작업을 다시 하려고 하면 처음부터 다시 해주어야 하는 경우가 많습니다. 하지만 T-SQL 문을 작업을 하고 저장해 둔 후 다음에 다시 이용 할 수 있는 장점이 있습니다. 그리고 예기치 않게 EM을 사용하지 못하게 되는 경우 T-SQL 문에 익숙한 관리자는 작업을 별 문제 없이 해 낼 수 있지만 EM에만 의존했던 관리자는 아무 작업도 못하고 발만 동동 구르게 됩니다. T-SQL 문을 통한 관리 방법을 꼭 익히시기 바랍니다.

1) EM을 이용한 백업 장치 만들기

① 아래 [그림 1]과 같이 백업 장치를 만드는 부분을 EM에서 볼 수 있습니다.

[그림 1]

② 아래 [그림 2] 처럼 "백업" 위에서 마우스 오른쪽 버튼을 눌러 표시되는 단축메뉴에서 "새 백업 장치(N)"를 선택하면 [그림 3] 과 같은 대화창이 뜹니다.

[그림 2]

③ 아래 [그림 3] 처럼 "이름" 부분에 원하는 장치명을 입력합니다. 되도록 의미가 있는 이름으로 하시는게 좋습니다. 그리고 "파일 이름" 부분에는 실제로 물리적인 경로가 어떻게 되는지 정해 주시면 됩니다. 결로을 정해주시면 기본적으로 입력한 "이름" + ".bak" 이 물리적인 파일 이름으로 지정됩니다.

[그림 3]

④ 아래 [그림 4] 는 sqlworld 라는 이름으로 백업장치가 만들어진 결과를 보여 줍니다.

[그림 4]

[참고] 위와 같은 과정으로 백업장치를 만들었다고 해서 지정했던 물리적인 경로의 파일(위의 경우는 E:\Data\sqlworld.bak")이 즉시 생기지 않습니다. 실제로 물리적인 파일이 생기는 시점은 첫번째 백업이 이루어지는 때입니다.

2) T-SQL 문을 이용한 백업 장치 만들기

T-SQL 문을 이용한 백업장치 만들기는 아주 간단합니다. 위에서 EM을 통해서 했던 작업을 T-SQL 로 한다면 다음과 같습니다.

USE master GO EXEC sp_addumpdevice 'disk', 'SQLWORLD', 'E:\Data\sqlworld.bak'

o disk : 하드 디스크로 덤프 받음을 의미합니다.(이외에 pipe, tape 등이 있습니다)
o SQLWORLD : 논리적인 백업 장치 명입니다.
o E:\Data\sqlworld.bak : 물리적인 파일의 경로와 파일명입니다.

sp_addumpdevice 저장프로시져에 대한 자세한 설명을 원하시면 온라인 설명서(Books Online)을 참조하여 주시기 바랍니다.

이제 우리가 만든 백업장치에 백업을 받으면 됩니다. 이어지는 전체 백업(Full Backup) 관련 강좌에서는 백업장치를 이용하여 백업을 받는 방법과 백업장치를 이용하지 않고 백업을 받는 방법에 대하여 살펴보도록 하겠습니다.

========================================================================================

Full Backup

========================================================================================

1. 전체 백업(Full Backup)의 특징

여러가지 백업 방법을 설명하면서 전체 백업에 대해서도 간단히 언급을 했었습니다. 전체 백업은 말 그대로 데이터베이스 전체를 백업 받는 것을 의미합니다. 데이터베이스 사이즈가 작은 경우라면 전체 백업을 받는데 별 무리가 없겠지만 데이터베이스 사이즈가 큰 경우라면 전체 백업은 그리 좋은 방법이 아닙니다. 전체 백업을 사용할 수 있는 경우를 생각해 본다면 다음과 같지 않을까 생각합니다.

o 개발용 데이터베이스를 현재 상태로 보관하고 싶은 경우 전체 백업
o 데이터베이스를 다른 서버로 옮기고 싶은 경우 전체 백업해서 다른 서버에 전체 복원
o 데이터베이스에 어쩌다 가끔 변경이 생기는 경우 이를 보관하기 위해 전체 백업
o 현재의 Master 데이터 베이스 보관을 위해 전체 백업

몇가지 생각나는 경우를 적어 보았는데 이게 정답이 아닐 수 도 있습니다.

2. 전체 백업 수행

그럼 전체백업을 받는 과정을 보도록 하겠습니다. 여기서는 다음과 같이 3가지 경우를 살펴 보도록 하겠습니다.

o EM에서 백업 장치를 이용한 전체 백업
o EM에서 물리적인 파일을 이용한 전체 백업
o T-SQL 문을 이용한 전체 백업

1) EM에서 백업 장치를 이용한 전체 백업

바로 이전의 강좌에서 만든 SQLWORLD라는 이름의 백업장치가 있었습니다. 이 백업 장치에 sqlworld 데이터베이스를 전체 백업받는 과정을 보도록 하겠습니다. 강좌를 보시는 분들은 연습용 데이터베이스가 없으면 pubs 데이터베이스를 백업받아 보시기 바랍니다.

① sqlworld데이터베이스 위에서 마우스 오른쪽 버튼을 눌러 [모든작업] - [데이터베이스 백업] 을 선택하시면 아래 [그림 1] 과 같은 대화 창이 표시됩니다.

[그림 1]

② "이름(N)" 부분과 "설명(R)" 부분에는 참고할 만한 내용을 입력합니다. 위 [그림 1] 에서 처럼 빨간색 라인 부분을 보면 백업 방법이 "데이터베이스-전체(D)" 로 되어 있습니다. 즉 전체 백업을 받음을 의미합니다. 백업 대상 부분의 현재 내용이 있으면 [제거(M)] 버튼을 눌러 제거 하시고 [추가(A)] 버튼을 눌러 아래 [그림 2] 처럼 장치 선택화면이 나오도록 합니다. 그리고 "백업장치(B)" 부분에 이미 만들어져 있는 SQLWORLD 라는 백업장치를 선택하고 [확인] 버튼을 누릅니다.

[그림 2]

③ 아래 [그림 3]을 보시면 "덮어쓰기" 부분이 보입니다. 이 부분은 만일에 백업 장치에 기존에 백업 받은 내용이 있으면 여기에 추가 할 것인지(미디어에 추가) 아니면 기존의 내용을 지우고 쓸 것인지(기존 미디어 덮어쓰기)를 선택하는 것입니다. 처음 백업 받는 것이므로 현재는 무엇을 선택하든 아무 의미가 없습니다. "예약" 부분은 스케쥴을 설정하여 백업받는 것인데 다음에 자세히 다루도록 하겠습니다.

[그림 3]

④ 위 화면에서 [확인] 버튼을 누르면 백업 진행 상황이 보이면서 실제 백업이 이루어 집니다. 백업이 완료 되었으면 백업 장치를 만들 때 지정했던 물리적인 폴더에 실제 백업 파일이 생성되었나 확인해 보시기 바랍니다.

⑤ SQLWORLD 백업 장치의 [등록정보]를 열어 [내용보기] 버튼을 누르면 아래 [그림 4]와 같이 백업 받은 히스토리가 표시됩니다.

[그림 4]

[실습 1] 다음을 실습해 보시기 바랍니다.

위 과정을 이용하여 같은 데이터베이스(예제의 경우 sqlworld)를 동일한 백업장치(예제의 경우 SQLWORLD)에 여러번 백업을 받아 보시기 바랍니다. 백업을 받는 과정에서 [그림 3]의 "덮어쓰기" 부분을 바꾸어 가면서 백업을 받아 보시기 바랍니다. 그리고 [그림 4]와 같이 백업 히스토리를 확인해 보시기 바랍니다. "덮어쓰기"를 어떻게 선택하냐에 따라 변화된 히스토리를 볼 수 있을 것입니다.

2) EM에서 물리적인 파일을 이용한 전체 백업

위 백업 방법과 대부분 동일 합니다. 단 [그림 2]에서 "백업 장치" 대신 "파일 이름" 부분에 원하는 물리적인 파일의 경로와 파일의 이름을 설정해 주면 됩니다. 자세한 설명은 생략하도록 하겠습니다. 직접 해보시기 바랍니다.

3) T-SQL 문을 이용한 전체 백업

BACKUP DATABASE 문을 이용해서 백업 장치 또는 물리적인 파일에 직접 백업을 받을 수 있습니다. 몇가지 다양한 예를 들어 보도록 하겠습니다.

[예제 1]

USE Master GO BACKUP DATABASE sqlworld TO SQLWORLD WITH INIT

o sqlworld 데이터베이스를 SQLWORLD 라는 백업 장치로 백업을 받습니다.
o WITH INIT 옵션은 기존의 백업 내용이 있으면 덮어 쓰라는 설정입니다.

[예제 2]

USE Master GO BACKUP DATABASE sqlworld TO SQLWORLD WITH NOINIT

o sqlworld 데이터베이스를 SQLWORLD 라는 백업 장치로 백업을 받습니다.
o WITH NOINIT 옵션은 기존의 백업 내용을 보존하고 거기에 추가하여 백업 받으라는 설정입니다. 결국 백업 파일의 사이즈는 증가하게 됩니다. INIT 이나 NOINIT을 설정하지 않으면 NOINIT이 기본으로 적용됩니다.

[예제 3]

USE Master GO BACKUP DATABASE sqlworld TO DISK = 'E:\Data\sqlworld.bak' WITH NOINIT

o sqlworld 데이터베이스를 E:\Data\sqlworld.bak 라는 물리적인 파일로 백업을 받습니다.
o 앞의 [예제 1]과 [예제 2]와는 다르게 TO 대신 TO DISK 로 바뀌었음을 숙지하시기 바랍니다.

BACKUP DATABASE 에 대한 자세한 설명은 온라인 설명서(Books Online)을 통해 필히 확인하시기 바랍니다. 다양한 옵션들이 제공됩니다. 물론 이들이 자주 사용되는 옵션은 아니지만 이런것도 있구나..라고 확인은 해보시기 바랍니다. 다음 강좌에서는 전체 백업 받은 것을 복구하는 과정을 살펴보도록 하겠습니다.

========================================================================================

Full Backup Restore

========================================================================================

1. 데이터베이스 복원(Restore)이란?

데이터베이스 복원이란 백업받은 것을 사용가능한 데이터베이스로 원위치 시키는 작업을 이야기 합니다. 데이터베이스를 운영하는 동안에 한번도 복원을 할 일이 없다면 좋겠지만 분명 언젠가는 복원을 해야하는 경우가 발생하므로 그 방법을 미리 알고 있어야 합니다. 특히 백업받은 내용이 정확한지 혹은 백업받은 내용이 물리적으로 손상되지는 않았는지 모의 복원을 통해 확인해보는 것도 중요하다고 생각합니다. 복원 방법을 전혀 모르는 상태에서 데이터베이스에 심각한 문제가 생겼을 때 백업 파일을 가지고 발만 동동 구르는 일이 없어야 하겠습니다. 데이터베이스 복원을 하게 되는 경우를 찾아본다면 다음과 같지 않을까 생각합니다.

o 데이터베이스에 심각한 문제가 발생하여 복원을 통한 복구가 불가피한 경우
o 다른 서버에서 백업받은 내용을 새로운 서버에 복원해야 하는 경우
o 백업받은 내용을 가지고 실제 데이터베이스와 비슷한 테스트용 데이터베이스를 만들고자 하는 경우

2. 전체 백업을 이용한 데이터베이스 복원 작업

바로 이전의 강좌에서 우리는 sqlworld 데이터베이스를 전체 백업 받았습니다. 이 전체 백업을 이용해서 데이터베이스 복원하는 방법을 살펴보도록 하겠습니다. 나중에 살펴보도록 하겠지만 차등 백업이나 트랜잭션 백업을 받은 경우의 데이터베이스 복원은 더 복잡합니다.

이 강좌에서는 다음의 두가지 방법을 이용한 데이터베이스 복원 방법을 살펴보도록 하겠습니다.

o 데이터베이스가 연결되어 있는 경우 EM에서 데이터베이스 복원
o 데이터베이스가 연결되어 있지 않는 경우 EM에서 데이터베이스 복원
o T-SQL 문을 이용한 데이터베이스 복원

1) 데이터베이스가 연결되어 있는 경우 EM에서 데이터베이스 복원

① 아래 [그림 1]와 같이 sqlworld 데이터베이스를 선택한 상태에서 [도구] 메뉴에서 "데이터베이스 복원(R)" 을 선택하시면 데이터베이스 복원을 위한 대화창이 표시됩니다.

[그림 1]

② [그림 2]와 같은 대화창이 표시되면 [도움말] 버튼을 눌러 각 항목에 대한 의미를 살펴보시기 바랍니다. 빨간색 부분에서 복원할 백업내역을 선택하고 [확인] 버튼을 누르면 복원이 진행됩니다.

[그림 2]

2) 데이터베이스가 연결되어 있지 않는 경우 EM에서 데이터베이스 복원

① 아래 [그림 3]을 보시면 sqlworld 데이터베이스가 제거된 상태입니다. 이 상태에서 복구를 하는 방법은 약간 다릅니다. 위의 방법과 동일하게 [도구] 메뉴에서 "데이터베이스 복원(R)" 을 선택하시면 데이터베이스 복원을 위한 대화창이 표시되나 [그림 2]와 같이 sqlworld 데이터베이스에 대한 백업 내역이 표시되지 않습니다.

[그림 3]

② [그림 4]와 같이 대화창이 표시되면 "데이터베이스로 복원" 부분에 sqlworld라고 입력하고 [장치내용]을 선택하고 [장치선택] 버튼을 누르면 [그림 5]와 같이 백업 장치나 백업 파일을 선택하는 대화창이 표시됩니다.

[그림 4]

③ [그림 5]와 같이 대화창이 표시되면 [추가] 버튼을 눌러 표시되는 "복원할 위치 선택" 창에서 SQLWORLD 백업장치를 선택하면 됩니다. 백업 장치가 아니고 물리적인 파일인 경우는 "파일이름" 부분을 선택하고 물리적인 파일을 지정하시면 됩니다.

[그림 5]

④ [그림 6]은 백업 장치 선택이 끝난 화면입니다. 이 화면에서 [내용보기] 버튼을 누르면 백업 내역이 표시되며 이 중에서 복원하고자 하는 시점의 백업 내역을 선택하시면 됩니다.

[그림 6]

3) T-SQL 문을 이용한 데이터베이스 복원

RESTORE DATABASE 문을 이용해서 백업 장치 또는 물리적인 파일로부터 데이터베이스를 복원 할 수 있습니다.

[예제 1]

USE Master GO RESTORE DATABASE sqlworld FROM SQLWORLD

o sqlworld 데이터베이스를 SQLWORLD 라는 백업 장치로부터 복원합니다.

[예제 2]

USE Master GO RESTORE DATABASE sqlworld FROM DISK = 'E:\Data\sqlworld.bak'

o sqlworld 데이터베이스를 E:\Data\sqlworld.bak 라는 물리적인 파일에서 직접 복원 합니다.

RESTORE DATABASE 에 대한 자세한 설명은 온라인 설명서(Books Online)을 통해 필히 확인하시기 바랍니다. 다양한 옵션들이 제공됩니다. 물론 이들이 자주 사용되는 옵션은 아니지만 이런것도 있구나..라고 확인은 해보시기 바랍니다. 다음 강좌에서는 차등 백업과 트랜잭션 백업에 대하여 살펴보도록 하겠습니다.

========================================================================================

Differential Backup/Restore

========================================================================================

1. 차등 백업(Differential Backup)의 특징

데이터베이스 차등 백업이란 전체 백업(Full Backup) 이후에 변경된 데이터만 백업받는 방법입니다. 예를 들어서 현재 100GB 정도의 데이터베이스가 있고 어제 저녁에 전체 백업을 받았다고 가정 하겠습니다. 오늘 하루 동안 입력된 데이터가 1,000건 정도 있다고 했을 때 가장 효과적인 백업(트랜잭션 백업을 제외하고)은 오늘 변경된 1,000 건의 데이터에 대한 백업만 받는 것입니다. 이후에 문제가 발생하게 되면 어제 받은 전체 백업을 복원하고 오늘 추가로 백업 받은 변경분 백업만 이어서 복원하게 되면 전체 데이터가 복원됩니다.

그래서 차 등백업이 효과적인 경우는 전체 데이터베이스 사이즈가 너무 커서 전체 백업을 받기에 너무 부담이 되는 경우입니다. 단지 변경 내용만 백업 받게 되면 짧은 시간에 백업을 받을 수 있어 효과적입니다. 중요한 것은 차등 백업만 있으면 아무 의미가 없다는 것입니다. 전체 백업 받은 것이 있어야 차등 백업이 의미가 있습니다. 다시 말하면 문제가 발생하여 데이터베이스 복원을 해야 하는데 차등 백업 데이터는 있는데 전체 백업 데이터가 없다면 복원이 불가능하게 됩니다.

혼동하기 쉬운 차등 백업의 특징이 있습니다. 차등 백업은 전체 백업이 이루어지고 난 이후에 변경된 내용을 백업 받는다고 했습니다. 이 사실을 정확히 이해 하셔야 합니다. 다음의 가정을 세우도록 하겠습니다.

① 고객 테이블에 홍길동 고객정보추가
② 전체 백업
③ 고객 테이블에 안경태 고객정보 추가
④ 차등 백업
⑤ 고객 테이블에 김치국 고객정보 추가
⑥ 차등백업
⑦ 문제 발생

이렇게 문제가 발생 한 경우 데이터베이스 복원 작업을 하려고 합니다. 과연 이때 필요한 백업 데이터는 어떤것들 일까요?

위 경우 ④ 번의 차등 백업은 안경테 고객의 데이터를 가지고 있습니다. 그리고 ⑥ 번의 차등 백업은 김치국 고객의 정보 뿐만 아니라 안경태 고객의 정보도 가지고 있습니다. 왜냐하면 전체 백업 이후의 변경 데이터를 가지고 있기 때문입니다. 그래서 복원을 원한다면 전체 백업을 복원 한 후 ⑥ 번의 차등 백업을 복원 하면 데이터베이스 복원이 이루어지게 되는 것입니다.

2. 차등 백업 수행

1) EM에서의차등 백업

차등 백업을 받는 방법은 전체 백업을 받는 과정과 동일 합니다. 단지 아래 [그림 1]과 같이 백업 방법을 '데이터베이스 - 차등'으로 선택만 하시면 됩니다. 각 항목들의 의미는 전체 백업과 동일합니다.

[그림 1]

2) T-SQL 문을 이용한 전체 백업

BACKUP DATABASE 문을 이용해서 전체 백업은 물론 차등 백업을 수행 할 수 있습니다. 테이블을 하나 만들어 전체 백업과 차등 백업을 수행하고 그 결과를 보도록 하겠습니다.

[예제 1] Test1 테이블 만들기

CREATE TABLE Test1 ( col1 char(05), col2 int ) GO

o 간단한 구조의 테이블 Test1 을 만들었습니다.

[예제 2] 샘플 데이터 추가

INSERT INTO Test1 VALUES('AAAAA',10) INSERT INTO Test1 VALUES('BBBBB',20) INSERT INTO Test1 VALUES('CCCCC',30)

o 3개의 레코드를 추가 하였습니다.

[예제 3] 전체 백업 수행

USE Master GO BACKUP DATABASE sqlworld TO SQLWORLD WITH INIT

o sqlworld 데이터베이스를 예전에 만든 백업 장치인 SQLWORLD에 전체 백업을 받았습니다.

[예제 4] 샘플 데이터 추가

INSERT INTO Test1 VALUES('DDDDD',40) INSERT INTO Test1 VALUES('EEEEE',50) INSERT INTO Test1 VALUES('FFFFF',60)

o 다시 3개의 레코드를 추가 하였습니다.

[예제 5] 첫번째 차등 백업 수행

USE Master GO BACKUP DATABASE sqlworld TO SQLWORLD WITH DIFFERENTIAL, NOINIT

o 첫번째 차등 백업을 수행했습니다. NOINIT을 준 이유는 앞에서 백업받은 전체 백업을 보존하기 위해서 입니다. INIT으로 하게 되면 전체 백업 내용이 사라집니다.

[예제 6] 샘플 데이터 추가

INSERT INTO Test1 VALUES('GGGGG',70) INSERT INTO Test1 VALUES('HHHHH',80) INSERT INTO Test1 VALUES('IIIII',90)

o 다시 3개의 레코드를 추가 하였습니다.

[예제 7] 첫번째 차등 백업 수행

USE Master GO BACKUP DATABASE sqlworld TO SQLWORLD WITH DIFFERENTIAL, NOINIT

o 두번째 차등 백업을 수행했습니다.

3. 차등 백업으로 부터의 데이터베이스 복원

[예제 1] 에서 부터 [예제 7]까지의 과정에서 몇가지 데이터의 변화가 있었고 우리는 전체 백업 한번과 두번의 차등 백업의 과정으로 데이터베이스를 백업 받은 상태입니다. 이 시점에서 문제가 발생하여 sqlworld 데이터베이스를 복원해야 한다고 가정을 하고 복원 작업을 하도록 하겠습니다.

전체 백업으로 부터 복원하는 방법을 이전의 강좌에서 배웠습니다. 동일한 방법으로 sqlworld 데이터 베이스 복원을 시도하게 되면 아래 [그림 2]와같은 화면이 표시됩니다. 빨간색 부분을 보면 위에서 우리가 시도한 3번의 백업 히스토리가 보입니다. 그 중에서 파란색 부분은 두번의 차등 백업 히스토리입니다. [그림 2]에서는 맨 처음 전체 백업과 마지막 차등 백업이 선택되어 있습니다. 이때 전체 백업을 선택하지 않으려고 해도 할 수가 없습니다. 왜냐하먼 차등 백업은 전체 백업 내용이 복원 되어야 의미가 있기 때문입니다.

[그림 2]

위 [그림 2]의 상태에서 [확인] 버튼을 눌러 복원을 하면 전체 데이터가 복원 됩니다. 만일 전체 백업 내용과 첫번째 차등 백업 내용을 선택하고 복원을 하게 되면 Test1 테이블에는 6개의 레코드만 존재하는 상태가 됩니다. 마지막 입력된 3개의 레코드는 복원이 안되기 때문입니다.

========================================================================================

데이터베이스 옵션을 이용한 트렌젝션 로그제어

========================================================================================

1. 트랜잭션(Transaction) 이란?

트랜잭션(Transaction)이란 무언인가에 대하여 다음과 같이 온라인설명서에서는 이야기 하고 있습니다.

트랜잭션은 하나의 논리적 작업 단위로 수행되는 일련의 작업입니다. 작업의 논리적 단위는 ACID(원자성, 일관성, 격리성 및 영속성) 속성이라고 하는 네 가지 속성을 통해 트랜잭션으로서의 자격을 부여합니다. 원자성 트랜잭션은 더 이상 분류할 수 없는 작업 단위여야 하며 모든 데이터 수정 작업이 수행되거나 하나도 수행되지 말아야 합니다. 일관성 완료된 트랜잭션의 모든 데이터는 일관적이어야 합니다. 관계형 데이터베이스에서는 트랜잭션 수정에 모든 규칙을 적용하여 모든 데이터 무결성을 유지해야 합니다. 트랜잭션 마지막에는 B-tree 인덱스 또는 이중 연결 목록 등 모든 내부적 데이터 구조를 반드시 수정해야 합니다. 격리성 동시 트랜잭션에 의한 수정은 다른 동시 트랜잭션에 의한 수정과 격리되어야 합니다. 트랜잭션에서 다른 동시 트랜잭션이 수정하기 전 상태의 데이터를 보거나, 두 번째 트랜잭션이 완료된 후의 데이터를 볼 수는 있지만 중간 상태는 볼 수 없습니다. 결과적으로 시작 데이터를 다시 로드하고 일련의 트랜잭션을 재생하여 원래 트랜잭션이 수행된 후의 상태로 데이터를 되돌릴 수 있는데 이를 순차성이라고 합니다. 영속성 트랜잭션이 완료되고 나면 그 영향이 영구적으로 시스템에 적용됩니다. 수정은 시스템에 오류가 발생한 경우에도 지속됩니다.

이해하기가 상당히 어렵습니다.

로그 백업을 설명하기 위한 것이므로 트랜잭션에 대한 자세한 설명은 하지 않도록 하겠습니다. 이 강좌에서는 트랜잭션이란 데이터를 변경시키는 일련의 행위라고 생각해주시면 되겠습니다. 즉 테이블의 데이터를 수정(Update)하거나 삭제(Delete)하거나 추가(Insert)시키는 행위를 말합니다.

2. 트랜잭션 로그 백업 (Transaction Log Backup) 이란?

트랜잭션 로그 백업(일반적으로 로그 백업이라고 합니다)은 실제 데이터를 백업 받는게 아니고 데이터를 변경시킨 행위 자체를 백업받는것을 이야기 합니다. 예를 들어 고객 테이블에 '홍길동' 고객의 정보가 있는데 이 '홍길동' 고객이 이름이 '홍기동'으로 변경되었다고 한다면 데이터백업은 '홍기동' 이라는 데이터를 백업 받는 것이고 로그 백업은 '홍길동'의 이름이 '홍기동'으로 바뀌었다는 사실을 백업받은 것입니다. 만일 데이터에 문제가 생기는 이름을 바꾼 행위를 재수행 함으로써 데이터를 복원하게 됩니다.

SQL 서버는 관리자가 로그를 지우라는 설정을 하지 않는 한 문제 발생시 트랜잭션을 취소하거나 데이터베이스에 반영되지 않은 트랜잭션을 반영하기 위한 목적으로 로그를 남기게 됩니다. 이렇게 남는 로그는 관리자 입장에서 볼 때는 불의의 사태로부터 데이터를 복구 시킬 수 있는 중요한 수단이 됩니다.

또한 로그 백업은 데이터베이스의 사이즈가 너무 커서 데이터베이스 전체를 백업받기가 어려울 때 데이터의 손실을 막기 위한 또다른 백업 방법으로서 사용됩니다.

3. 로그에 대한 데이터베이스 설정

다음과 같은 내용을 데이터베이스 설정을 통하여 구현할 수 있습니다.

o트랜잭션 로그가 쌓이지 않게 하기
o 트랜잭션 로그가 쌓이게 하기
o 로그 파일의 사이즈가 무작정 커지지 않게 하기

1) 트랜잭션 로그가 쌓이지 않게 하기

트랜잭션 로그가 쌓이지 않게 하는 것은 실제 업무에서는 사용하지 않는게 좋습니다. 로그가 쌓이지 않게 하면 로그 파일이 무작정 증가하는 사태를 막을 수는 있으나 만일의 경우에 중요한 복구가 불가능 할 수 있기 때문입니다. 제 개인적인 생각으로는 시스템을 개발하는 동안에 테스트용 데이터가 기록되는 과정에서는 로그가 쌓이지 않게 설정을 해서 작업을 하고 실제 업무에 반영이 되는 시점에서는 로그가 쌓이게 하는게 좋습니다. 로그가 쌓이게 되면 관리자는 로그 백업을 이용해서 로그 사이즈가 계속 증가하는 사태를 막아야 합니다. (이에 대해서는 뒤에서 살펴보도록 하겠습니다)

로그가 쌓이지 않게 하기 위해서는 데이터베이스 복구 모델을 '단순(Simple)'로 하면 됩니다. SQL 서버 7.0의 경우는 데이터베이스 옵션 중에서 'trunc. log on chkpt' 옵션이 있는데 SQL 서버 2000에서는 복구모델을 이용해서 이 기능을 대신하게 됩니다. 물론 이전 버젼과의 호환을 위해서 'trunc. log on chkpt' 을 지정해주어도 되나 결과는 같습니다.

① 해당 데이터베이스 위해서 마우스 오른쪽 버튼을 눌러 표시되는 단축메뉴에서 [등록정보]를 선택하여 데이터베이스 등록 정보를 표시합니다.

② 다음 [그림 1]은 데이터베이스 옵션에서 복구모델을 '단순(Simple)'로 한 예입니다.

[그림 1]

복구 모델은 '단순' 으로 하게되면 커밋(Commit) 된 트랜잭션의 로그는 자동으로 제거되어 로그가 쌓이지 않게 됩니다.

만일 데이터베이스 복구 모델을 QA에서 변경하고자 하는 경우는 다음과 같이 ALTER DATABASE 를 이용하시면 됩니다.

USE Master GO ALTER DATABASE Sqlworld SET RECOVERY SIMPLE

o SIMPLE 위치에는 FULL 또는 BULK_LOGGED 가 지정될수 있습니다.

※ 복구 모델에 대한 자세한 설명을 원하시면 온라인설명서(Books Online)에서 '복구 모델'을 찾아보시기 바랍니다.

2) 트랜잭션 로그가 쌓이게 하기

로그가 쌓이게 하려면 위 [그림 1]에서 복구 모델을 '단순'이 아닌 '최대' 또는 '대량로그' 로 하시면 됩니다. 로그가 쌓이게 되면 로그가 증가함에 따라 로그 파일이 자동으로 증가하게 됩니다. (물론 데이터베이스 생성시 자동증가로 설정한 경우) 그러므로 이를 방치하게 되면 로그 파일이 무작정 증가하여 전체 하드디스크 공간을 차지하게 되는 경우가 있습니다. 실제 데이터베이스의 사이즈는 500MB인데 로그 사이즈는 50GB가 되는 경우도 있습니다.

로그 파일의 사이즈가 무작정 증가하지 않도록 하기 위해서는 정기적으로 로그 파일을 백업받아 주어야 합니다. 로그 백업은 만일의 경우에 대비해서 로그를 별도로 기록해주는 목적과 로그 사이즈를 줄여주는 목적 두가지를 가지고 있습니다.

로그 백업에 대해서는 다음 강좌에서 곧바로 다룰 것입니다. 우선 로그 사이즈 증가를 방지하기 위한 데이터베이스 옵션 설정에 대하여 살펴보도록 하겠습니다.

3) 로그 파일의 사이즈가 무작정 커지지 않게 하기

로그가 무작정 쌓이는 것은 백업을 통해서 방지 할 수 있다고 했습니다. 하지만 엄청난(?) 트랜잭션에 의해 발생된 로그로 인하여 로그 파일의 사이즈가 커진 상태에서 로그를 백업 받게 되면 로그는 줄어드나 커져버린 로그 파일의 사이즈는 별도의 설정이 없으면 자동으로 줄어들지 않게 됩니다.

로그 파일의 사이즈를 SQL 서버가 수시로 확인하여 불필요하게 커진 경우 그 안에 로그가 없으면(백업으로 인해) 로그 파일의 사이즈를 자동으로 줄어들게 할 수 있습니다. 이러한 설정은 데이터베이스의 '자동 축소' 옵션을 이용함으로써 가능합니다. 아래 [그림 2]은 '자동축소' 옵션을 선택한 화면의 예입니다.

[그림 2]

4. 정리

다음의 내용을 필히 기억해 주시기 바랍니다.

o 트랜잭션 로그는 만일의 사태에서 데이터 복구를 가능하게 하는 중요한 의미를 가지고 있어 함부로 지워서는 안됩니다.
o 실제 업무에서 데이터베이스를 사용 하는 경우는 데이터베이스 복구 모델을 '최대' 또는 '대량로그'로 설정하여 로그가 지워지지 않고 쌓이게 합니다.
o 쌓이는 로그를 정기적으로 백업 받아 로그가 꽉 차는 사태를 방지해야 합니다.
o 백업 받아 빈공간이 많아진 로그 파일을 자동으로 축소하기 위해서는 데이터베이스 옵션 중에서 '자동축소'를 선택하시면 됩니다.
o 자동 축소가 아닌 경우는 DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE을 이용해서 수작업으로 축소시켜 주어야 합니다.

현재 자신이 사용하고 있는 데이터베이스의 옵션을 살펴보고 복구모델은 어떻게 되어 있는지, 로그 백업은 어떻게 이루어지고 있는지 확인을 해보시기 바랍니다.

다음 강좌에서는 로그 백업을 받는 방법을 살펴보고 증가된 로그를 백업받고 축소시켜주는 내용을 실습해 보고자 합니다.

========================================================================================

트렌젝션로그 줄이기 테스트

========================================================================================

1. 테스트를 위한 임시 테이블 만들기와 데이터 추가하기

예전의 테스트 처럼 sqlworld 데이터베이스를 이용하여 실제 상황을 재현해 보도록 하겠습니다. 현재 sqlworld 데이터베이스의 옵션은 다음과 같습니다.

o 데이터베이스 복구모델 : 최대
o 자동 축소기능 : 사용안함

다음 [그림 1]은 현재 sqlworld의 데이터베이스와 로그 사이즈를 보여줍니다.

[그림 1]

우선 현재의 sqlworld 데이터베이스를 백업 받도록 하겠습니다.(모든 백업의 기본은 전체 백업입니다)

USE Master GO BACKUP DATABASE sqlworld TO DISK = 'E:\Data\sqlworld.bak'

1) 테이블 만들기

다음과 같이 테스트 테이블 Test_Log를 만들도록 하겠습니다.

USE sqlworld GO CREATE TABLE Test_Log ( col1 int PRIMARY KEY, col2 char(1000) )

다음과 같이 스크립트를 이용해서 레코드를 추가하도록 하겠습니다.

SET NOCOUNT ON GO DECLARE @num int SET @num = 1 WHILE @num < 5001 BEGIN INSERT INTO Test_Log VALUES(@num, REPLICATE('A',1000)) SET @num = @num + 1 END

위 작업을 통해서 오천개 정도의 레코드가 Test_Log 테이블에 추가되었습니다. 이로 인하여 sqlworld 데이터 사이즈와 로그 사이즈가 증가했습니다. 아래 [그림 2]가 그 내용을 확인해주고 있습니다.

[그림2]

만일 sqlworld 데이터베이스 복구 모델이 '단순' 이었다면 위와 같은 작업이 완료가 되면 로그가 자동으로 제거되어 [그림 2] 처럼 로그 사이즈가 증가하지 않을 것입니다.(이 내용이 잘 이해되지 않으면 바로 이전의 강좌를 다시한번 읽어보시기 바랍니다)

2) 데이터를 변경하여 데이터와 로그 사이즈 증가시키기

다음과 같이 데이터를 변경하고 삭제하여 로그 사이즈를 증가시켜 보겠습니다.

UPDATE Test_Log Set col2 = REPLICATE('B',1000) WHERE col1 < 1000 GO DELETE FROM Test_Log WHERE col1 < 2000 GO DELETE FROM Test_Log WHERE col1 < 4000

위 작업을 수행하니 Test_Log 테이블의 데이터 변경에 대한 로그와삭제에 대한 로그가 증가함에 따라 sqlworld 데이터베이스의 사이즈는 다음 [그림 3]과 같이 변하였습니다.

[그림3]

로그 파일은 0.99 MB에서 6.74 MB 로 사이즈가 증가함을 알 수 있습니다.

이처럼 트랜잭션이 발생함에 따라 로그가 계속 쌓이면서 로그 파일의 사이즈가 계속 증가함을 알 수 있습니다. 이제 확인할 내용은 로그 백업을 하면 로그가 줄어드는지, 그리고 로그 파일의 사이즈는 어떻게 되는지 하는 것입니다.

2. 트랜잭션 로그 백업을 통한 로그 줄이기

로그 백업은 예전에 배운 데이터베이스 백업과 비슷한 방법으로 이루어 집니다. EM을 통해서 할 수도 있고 QA에서 직접 BACKUP LOG 문을 이용하여 백업 받을 수도 있습니다. 이 테스트에서는 QA를 이용하여 현재 sqlworld 데이터베이스의 로그를 백업받도록 하겠습니다.

USE Master GO BACKUP LOG sqlworld TO DISK = 'E:\Data\sqlworld_log.bak'

수행하는 방법은 BACKUP DATABASE 문과 비슷합니다.

만일 로그를 백업 받지는 않고 단지 현재의 로그만 지우고 싶으면 다름과 같이 TRUNCATE_ONLY 옵션을 사용하면 됩니다.

USE Master GO BACKUP LOG WITH TRUNCATE_ONLY

이렇게 로그를 백업 받은 후의 sqlworld 데이터베이스의 사이즈를 보니 다음 [그림 4]와 같이 변한 것을 볼 수 있습니다.

[그림4]

로그 사이즈가 3.64 MB 에서 1.84 MB로 줄어 들었습니다. 하지만 로그 파일의 사이즈는 아직도 6.74 MB로서 원래 사이즈를 그대로 유지하고 있습니다. 결국 4.9 MB의 빈 공간이 놀고(?) 있게 됩니다.

3. DBCC SHRINKDATABASE을 이용한 로그파일 사이즈 줄이기

DBCC SHRINKDATABASE 을 이용해서 수작업으로 비어있는 로그 사이즈를 없애서 로그 파일의 사이즈를 축소해 보도록 하겠습니다.

USE Master GO DBCC SHRINKDATABASE(Sqlworld)

결과는 다음 [그림 5]와 같이 변한것을 알 수 있습니다.

[그림5]

로그 파일의 사이즈가 [그림 4]와 비교 할 때 6.74 MB에서 2.49 MB로 줄어든 것을 알 수 있습니다. DBCC SHRINKDATABASE에 의해 줄어드는 로그 파일의 사이즈는 실제 데이터 파일의 빈 공간이 많을 수록 효과가 좋습니다. DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE에 대한 내용은 이전의 데이터베이스에 대한 강좌를 참고해 주시기 바랍니다.

4. 정리

로그 파일 사이즈 문제를 가지고 질문을 하시는 경우가 상당히 많습니다. 지금까지 설명드린 내용이 도움이 되었으면 합니다. 데이터베이스 관리자는 항상 로그 파일에 대한 관심을 가지고 모니터링을 해야 합니다. 하드디스크의 여유 공간은 충분한지도 수시로 확인하셔야 합니다. 그렇지 않으면 TempDB가 꽉 찼다는 등의 오류가 발생하게 됩니다.

========================================================================================

백업과 복원 정리

========================================================================================

이번 강좌에서는 복원에 대한 전체적인 내용을 살펴 보도록 하겠습니다. 즉, 전체 백업과 차등 백업 및 로그 백업을 받은 상태에서 복원을 하는 순서와 방법을 살펴보게 됩니다.

1. 테스트를 위한 임시 테이블 만들기와 데이터 추가하기

우선 현재의 sqlworld 데이터베이스에 테스트 테이블을 만들고 백업을 받도록 하겠습니다.

1) 테이블 만들기

다음과 같이 테스트 테이블 Test_Log를 만들도록 하겠습니다.

USE sqlworld GO CREATE TABLE Test1 ( col1 int, col2 char(05) ) GO

2) 데이터 추가하기

다음과 같이 스크립트를 이용해서 레코드를 추가하도록 하겠습니다.

INSERT INTO Test1 VALUES(1,'AAAAA') INSERT INTO Test1 VALUES(2,'BBBBB') INSERT INTO Test1 VALUES(3,'CCCCC') INSERT INTO Test1 VALUES(4,'DDDDD') INSERT INTO Test1 VALUES(5,'EEEEE') GO

위 작업을 통해서 다섯개의 레코드가 Test1 테이블에 추가되었습니다.

2. 데이터베이스 백업

1) 데이터베이스 백업을 위한 백업 장치 만들기

다음과 같이 데이터베이스를 백업 받기 위한 백업 장치를 만들도록 하겠습니다.

EXEC sp_addumpdevice 'disk', 'sqlworld_dump', 'E:\Data\sqlworld_dump.bak' GO

※ 백업 장치 만드는 방법에 대해서 이해하고 있지 못하신 분들은 예전에 진행된 백업 장치 만들기 강좌를 참고하여 주시기 바랍니다.

2) 데이터베이스 전체 백업

앞에서 만든 sqlworld_dump 백업 장치에 현재까지의 sqlworld 데이터베이스를 전체 백업 받도록 하겠습니다.

BACKUP DATABASE sqlworld TO sqlworld_dump WITH INIT GO

이 전체 백업 작업으로 인해서 다섯개의 레코드가 추가된 Test1 테이블이 백업된 것을 알 수 있습니다.

※ 전체 백업에 대해 이해하고 있지 못하신 분들은 예전에 진행된 전체 백업 받기강좌를 참고하여 주시기 바랍니다.

3) 데이터 추가하기

다음과 같이 스크립트를 이용해서 다섯개의 레코드를 더 추가하도록 하겠습니다.

INSERT INTO Test1 VALUES(6,'FFFFF') INSERT INTO Test1 VALUES(7,'GGGGG') INSERT INTO Test1 VALUES(8,'HHHHH') INSERT INTO Test1 VALUES(9,'IIIII') INSERT INTO Test1 VALUES(10,'JJJJJ') GO

위 작업을 통해서 다섯개의 레코드가 Test1 테이블에 추가되었습니다. 이제 Test1 테이블에는 col1 컬럼이 1 에서 10까지의 값을 갖는 열개의 레코드가 기록되어 있습니다.

4) 데이터베이스 차등 백업

앞에서 만든 sqlworld_dump 백업 장치에 현재까지의 sqlworld 데이터베이스를 차등 백업 받도록 하겠습니다.

BACKUP DATABASE sqlworld TO sqlworld_dump WITH DIFFERENTIAL GO

이 차등 백업에 의해 앞의 3) 번에서 이루어진 다섯개의 레코드를 추가한 내용이 백업되었습니다.

※ 전체 백업에 대해 이해하고 있지 못하신 분들은 예전에 진행된 차등백업 받기 강좌를 참고하여 주시기 바랍니다.

5) 데이터 또 추가하기

다음과 같이 스크립트를 이용해서 다섯개의 레코드를 더 추가하도록 하겠습니다.

INSERT INTO Test1 VALUES(11,'KKKKK') INSERT INTO Test1 VALUES(12,'LLLLL') INSERT INTO Test1 VALUES(13,'MMMM') INSERT INTO Test1 VALUES(14,'NNNNN') INSERT INTO Test1 VALUES(15,'OOOOO') GO

위 작업을 통해서 다섯개의 레코드가 Test1 테이블에 추가되었습니다. 이제 Test1 테이블에는 col1 컬럼이 1 에서 10까지의 값을 갖는 열개의 레코드가 기록되어 있습니다.

6) 데이터베이스 차등 백업

앞에서 만든 sqlworld_dump 백업 장치에 현재까지의 sqlworld 데이터베이스를 차등 백업 받도록 하겠습니다.

BACKUP DATABASE sqlworld TO sqlworld_dump WITH DIFFERENTIAL GO

이 차등 백업에 의해 앞의 3)번과 5) 번에서 이루어진 열개의 레코드를 추가한 내용이 백업되었습니다.

7) 데이터 변경

다음과 같이 스크립트를 이용해서 col1 컬럼이 6보다 작은 다섯개의 레코드에 대하여 col2 컬럼을 전부 'XXXXX' 로 변경하였습니다.

UPDATE Test1 SET col2 = 'XXXXX' WHERE col1 < 6 GO

8) 트랜잭션 로그 백업

7) 번에서 이루어진 변경 처리를 로그 백업을 받도록 하겠습니다. 로그 백업을 위해 새로운 백업장치 sqlworld_log_dump 를 만들어 이 장치에 로그를 백업 받도록 하겠습니다.

EXEC sp_addumpdevice 'disk', 'sqlworld_log_dump', 'E:\Data\sqlworld_log_dump.bak' GO BACKUP LOG sqlworld TO sqlworld_log_dump WITH INIT GO

9) 데이터 추가 변경 (문제 발생)

다음과 같이 스크립트를 이용해서 col1 컬럼이 5보다 큰 코드에 대하여 col2 컬럼을 전부 'YYYYY' 로 변경하였습니다.

UPDATE Test1 SET col2 = 'YYYYY' GO

앗! WHERE 절을 적지 않아서 모든 레코드의 col2 컬럼이 'YYYYY'로 바뀌어 버렸습니다.

3. 현재까지 백업된 내용 정리

지금까지 이루어진 4번의 백업 작업에 의해 Test1 테이블에 대해서는 다음과 같은 내용이 백업되었습니다.

1) 전체 백업 : col1 컬럼에 1 부터 5 까지 다섯개의 레코드
2) 첫번째 차등 백업 : col1 컬럼에 6 부터 10 까지 다섯개의 레코드
3) 두번째 차등 백업 : col1 컬럼에 6 부터 15 까지 열개의 레코드
4) 로그 백업 : col1 컬럼이 6 보다 작은 다섯개의 레코드에 대해 col2 컬럼을 'XXXXX' 로 변경 한 행위

4. T-SQL을 이용한 데이터베이스 복원

현재의 상황에서 앞에서 수행되었던 모든 백업을 복원하여 col2 컬럼이 'YYYYY'로 바뀐 사태를 수습하도록 하겠습니다.

1) 전체 백업 복원

USE master GO RESTORE DATABASE sqlworld FROM sqlworld_dump WITH FILE =1, NORECOVERY GO

o NORECOVERY 옵션으로 전체 백업 받은 내용을 복원 했습니다.
o FILE = 1은 sqlworld_dump 백업 장치에 여러 가지 백업(한번의 전체 백업과 두번의 차등 백업)이 있으므로 이 중에서 첫번째, 즉 전체 백업을 선택한다는 의미입니다.

2) 차등 백업 복원

RESTORE DATABASE sqlworld FROM sqlworld_dump WITH FILE = 3, NORECOVERY GO

o NORECOVERY 옵션으로 차등백업 받은 내용을 복원 했습니다.
o FILE = 3 은 sqlworld_dump 백업 장치에 여러 가지 백업(한번의 전체 백업과 두번의 차등 백업)이 있으므로 이 중에서 세번째, 즉 두번째의 차등 백업을 선택한다는 의미입니다.
o 결국 첫번째의 차등 백업은 사용될 필요가 없습니다.

3) 로그 백업 복원

RESTORE LOG sqlworld FROM sqlworld_log_dump WITH RECOVERY GO

o 로그 복원이 복원의 마지막 단계 이므로 RECOVERY 옵션을 사용하였습니다.

5. 과제

다음의 내용을 구현해 보시기 바랍니다.

1) 위에서 T-SQL로 구현한 복원을 EM(Enterprise Manager)를 이용해서 구현해 보세요.
2) 첫번째 차등 백업, 즉 col1 컬럼이 1부터 10까지만 가지고 있는 내용을 복원해 보세요.

부족한 부분은 관련된 강좌를 다시한번 확인하신 후 처리해 보시기 바랍니다.