'트랜잭션 로그 백업' 태그의 글 목록

Search

'트랜잭션 로그 백업'에 해당되는 글 2건

[MS-SQL2011강좌] 25. 백업과 복구 전략 by 강동운님

프로그램&DB/MS-SQL 2011. 8. 23. 09:53 Posted by Josep.H.S

이 내용은 2011년 6월 현재 SQL2011(코드명 Denali) Beta를 기준으로 작성 되었으며 SQL2011(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.
아울러, 현재 Beta버전이지만 강좌를 따라해 보시는데 아무 문제 없으실거에요. 도움 되시길 바랍니다.
==================================================================================================

안녕하세요. 이스트럭(강동운) 입니다.

이번에는 백업과 복구 전략에 대해서 설명드리겠습니다.

원문: http://www.sqler.com/127360

USE master

--백업을테스트할DB 생성

CREATE DATABASE bkupTest

먼저 테스트할 데이터베이스를 기본 옵션으로 생성합니다.

아시다시피 가장 기본적인 세팅이지요?

간단히 데이터베이스의 정보를 보도록 하지요.

sp_helpdb bkupTest

어떠세요? 흥미있게 보실 부분으로..

status라는 부분을 보시면? Recovery=FULL 이라는게 있을 겁니다.

기본 옵션으로 데이터베이스를 생성하면 복구 모델은 FULL이라는 거지요.

백업의 타겟은 크게 두가지로 나눌수 있습니다.

디바이스와 파일 입니다.

디바이스라는 가상의 장치 또는 테입장치 등을 연결해 백업을 하거나..

파일단위로 백업을 하실 수 있습니다.

최근의 상황을 볼때 디스크가 매우 저렴해졌지요. 디바이스 백업을 수행시

디바이스라는 하나의 장치에 여러 백업을 두게 되므로 편할 수 있을지 모르나..

독립적인 파일로 위치시키실 경우 조금 더 눈으로 쉽게 확인이 가능하므로

코난이의 경우 유지관리계획에 등록시켜 파일로 생성해 두는 것을 좋아 한답니다.

참고로 제가 말씀 드리는 디바이스는 영구 디바이스라고도 불리며

파일 단위 백업은 임디 시바이스라고도 합니다.

차근차근 보도록 하겠습니다.

먼저 파일로 백업하는 경우 입니다.이렇게 테이블을 생성하고 데이터를 넣은 후 백업을 진행 했습니다.

--테이블을생성합니다.

USE bkupTest

CREATE TABLE tblBkupTest(

name varchar(10)

, age int

)

INSERT INTO tblBkupTest(name, age) VALUES('코난', 20)

INSERT INTO tblBkupTest(name, age) VALUES('까까', 30)

--파일로백업

SET LANGUAGE '한국어'

BACKUP DATABASE bkupTest TO DISK='C:\bkupTest.bak' WITH INIT

백업 후 결과는

파일2에서데이터베이스'bkupTest', 파일'bkupTest'에대해296개의페이지를처리했습니다.

파일2에서데이터베이스'bkupTest', 파일'bkupTest_log'에대해2개의페이지를처리했습니다.

BACKUP DATABASE이(가) 298개의페이지를0.437초동안처리했습니다(5.312MB/초).

식으로 결과를 보실 수 있을 겁니다. - 결과역시 중요합니다. 특히 백업 수행 시각을

자세히 보는 습관을 들이세요.

복구는 어떻게 할까요? 간단히 해 보도록 할까요?

--테이블삭제

DROP TABLE tblBkupTest

--데이터조회불가.

SELECT * FROM tblBkupTest

--masterDB 사용- 해당DB가사용중이면복구가불가하기때문

USE master

--복구

restore database bkupTest from disk ='C:\bkupTest.bak'

WITH REPLACE

USE bkupTest

--데이터조회

SELECT * FROM tblBkupTest

복구시 완료 메세지는 다음과 같을 겁니다.

파일1에서데이터베이스'bkupTest', 파일'bkupTest'에대해296개의페이지를처리했습니다.

파일1에서데이터베이스'bkupTest', 파일'bkupTest_log'에대해6개의페이지를처리했습니다.

RESTORE DATABASE이(가) 302개의페이지를0.557초동안처리했습니다(4.224MB/초).

역시나 주의하실 점으로 항상 복구 완료 시각을 눈여겨 보시길 바랍니다.

위의 케이스는 단순히 데이터베이스를 풀 백업 했습니다. WITH INIT은 해당 백업 타겟을

초기화 시키고 백업을 진행하라는 의미 이지요. - 백업의 여러 옵션은 천천히

다룰 겁니다.

아울러 풀 백업을 restore 구문으로 복구 했습니다.

DROP으로 날린 테이블이 잘 복구가 되었지요?

복구하기 전에 USE master 구문으로 master DB에서 복구를 진행한 이유는..

해당하는 DB가 사용중이면 복구가 불가하기 때문입니다.

만약 넷웍을 통해 사용자가 붙을 가능성이 있다면.. 잠시 사용자를..

sp_who 명령으로 SPID를 보신후.. KILL <SPID번호>로 죽이시고 수행하시면 되며

처음 SQL서버를 시작할때.. 시작 매개변수를 주어서..

sqlservr.exe -m

형식으로 단일사용자 모드로 SQL서버 시작을 하시고.. 복구를 하시는 것도 좋은

방법 입니다.

계속 이야기를 진행하죠.. 파일로 백업(임시 디바이스)하고 복구하는걸 해 보셨죠..

이젠 개인적으로 별로 사용하지 않지만.. 영구 디바이스를 생성하고

백업하고 복구하는것을 해 보도록 하지요.

--영구백업디바이스생성

sp_addumpdevice 'disk', 'bkupTestDevice','c:\bkupTestDevice_full_bkup'

--디바이스로백업할때는?

BACKUP DATABASE bkupTest TO bkupTestDevice

결과를 보시면?

'Disk' 장치를 추가했습니다.

라는 결과와 함께 디바이스가 추가 된걸 아실 겁니다.

아울러.. 디바이스에 백업 완료시 메세지는..

파일3에서데이터베이스'bkupTest', 파일'bkupTest'에대해296개의페이지를처리했습니다.

파일3에서데이터베이스'bkupTest', 파일'bkupTest_log'에대해2개의페이지를처리했습니다.

BACKUP DATABASE이(가) 298개의페이지를0.444초동안처리했습니다(5.227MB/초).

식일 겁니다.

--테이블삭제

DROP TABLE tblBkupTest

--데이터조회불가.

SELECT * FROM tblBkupTest

--masterDB 사용- 해당DB가사용중이면복구가불가하기때문

USE master

--복구

restore database bkupTest from bkupTestDevice

WITH REPLACE

USE bkupTest

--데이터조회

SELECT * FROM tblBkupTest

복구 시 메세지는

파일1에서데이터베이스'bkupTest', 파일'bkupTest'에대해296개의페이지를처리했습니다.

파일1에서데이터베이스'bkupTest', 파일'bkupTest_log'에대해6개의페이지를처리했습니다.

RESTORE DATABASE이(가) 302개의페이지를0.491초동안처리했습니다(4.792MB/초).

식일 것입니다. 아울러 데이터를 조회해 보면 잘 된 것을 아실 겁니다.

많은 분들이 여기까지는 쉽게 이해하시고 잘 하시는데..

다음에 보시는 로그 백업에 대해서 많이 이해를 못하시지요.

이제 전략적인 측면에서.. 전체 / 로그 / 차등 백업을 이해해 보도록 하지요.

1. 풀백업만을 진행할 경우

먼저 이러한 타임라인이 있다고 생각해 봅시다.

주단위이며.. 위는 월요일 / 화요일 / ... 식으로 나올 겁니다.

두번째로. 시각은 08시부터 회사의 업무 시간인 6시까지 있을 겁니다.

이럴때 풀백업만을 진행할 경우 입니다. 풀 백업은 매일 아침 08시 업무가 시작되기 전에

이루어 집니다. 매일매일 하루에 한번씩 08시에 풀 백업을 받는 거지요!

이렇게 운영하시는 분들이 대단히 많을 겁니다. 문제 상황을 생각해 보지요.

1. 로그를 백업하지 않으므로 로그가 계속 불어나는 상황이 발생할 수 있다.

로그는 데이터의 변경본 입니다. 백업하면 지워지지만 지우지 않으므로.. 데이터는

50메가인데.. 로그는 1기가 이런 사태가 발생하게 되지요.

2. 문제 발생시 복구 가능한 데이터가 가장 적다.

실제 문제 상황을 생각해 봅시다.

자 이렇게 12시 30분에 데이터베이스가 깨졌다고 가정해 봅시다.

언제까지의 데이터로 복구가 가능할까요? - 진짜 쉽죠? ^_^;;

가장 최근의 백업본이 월요일 08시 이니..

월요일 08시의 데이터로만 복구가 가능한 겁니다. - 물론 한번도 백업한 적이 없다면?

복구 불가 하지요. -_-;;

08시부터 4시간 30분간의 데이터는 복구가 불가해 지는 겁니다. 아득하지요.

만약 다른 분 말씀으로.. "그렇다면 풀백업을 한시간에 하나씩 받으면 되자나요"

하지만.. 여기에 맹점이 있을 수 있지요.

SQL2000부터는 대단히 백업작업이 가벼워 진 편입니다. 온라인상에서 백업을 진행해도

큰 무리없이 진행이 가능하지요. 하지만.. 대부분의 일반적인 회사에서..

데이터의 용량은 대단히 큽니다. 어느곳은 데이터가 1기가를 훌쩍 넘기도 하지요.

만약 이럴때 한시간에 한번씩 백업을 한다면? 대단히 많은 부하가 걸려 백업만 하다가

시스템 리소스를 다 잡아 먹힐수도 있게 되겠지요. 그래서 좋은 방법이 될 수 없습니다.

다른 방법으로 풀백업 + 로그 백업의 방법이 있습니다.

2. 풀백업 + 로그 백업

이러한 식으로.. 매일 아침 08시 풀 백업.. 매시간마다 로그를 백업하는 겁니다.

그럼 어떨까요? - 이때의 제약 사항으로 SQL서버의 해당 DB는 반드시 단순 모델이 아니어야

합니다. SQL서버의 데이터베이스가 단순 모델이면? 로그 백업이 불가해 집니다.

- 샘플에서 보여 드릴 거구요.. - 전체 모델이나.. 또는 대량 로그(Bulk Load)모드

이어야 합니다.

"저렇게 한시간에 한번씩 로그를 백업하는것도 부하가 대단히 많지 않나요?"

라고 생각하실지도 모르지만.. 로그는 대단히 그 크기가 작습니다.

예를들어 게시판을 생각해 보세요. INSERT UPDATE DELETE 작업은 대단히 작지만..

실제 내용은 수천건이 들어가 있을 수 있지요. 실제 회사의 기간 데이터일 경우는

더더욱 그렇습니다. 이제 실제 문제 상황을 생각해 보도록 하지요.

이럴 경우는 어디까지 복구가 가능할까요?

이렇게 보는 바와 같이.. 08시 부터의 데이터가 저렇다고 생각해 보도록 하지요.

08시에 풀 백업본이 있었지요? 풀 백업본을 리스토어 하면? 데이터는 어떻게 될까요?

네 맞습니다. 위와 같은 데이터가 살아나겠지요?

그러면.. 우리는 여기까지만 복구가 가능한 걸까요? 아니요!!!

우리에겐 로그 백업본이 있었습니다. 09시의 로그 백업본을 리스토어 하면?

네 맞습니다.

이렇게 데이터가 수정된 녀석으로 복구가 되겠지요?

즉!! 로그는 데이터의 변경이므로.. 데이터의 변경을 그대로 적용시켜 나가면?

우리의 데이터가 나오게 되겠지요!!! - 이작업을 REDO라고 말하기도 합니다.

여기서 문제 입니다. 9시, 10시, 11시, 12시 까지의 로그를 잘 리스토어 했습니다.

여기까지 데이터를 잘 복구 했군요.

그렇지만.. 이후 12시 20분의 데이터는?

로그를 백업하지 못했지요? 그러므로 복구가 불가해 지는 겁니다.

단!!! SQL2000의 전체 복구 모델일 경우는 12시 20분뿐 아니라... 12시 29분 까지 복구가

가능합니다. - 이유는? 데이터베이스가 깨졌지만!!! 만약 데이터 파일만 깨지고

로그가 깨진것이 아니라면? 로그를 데이터베이스가 깨진 후 백업할 수 있습니다.

- 안될것 같지만 됩니다. - 그후!! 이 로그를 복구하면? 12시 29분까지의 데이터를

복구할 수 있게 되는 것이지요.

이 샘플은 잠시 후 보여 드리기로 하구요.

마지막으로 차등 모델에 대해서 간략하게 말씀 드리지요.

3. 차등 백업

로그 백업의 범위는 이렇습니다. 정확히 마지막 로그 백업 이후의 로그 데이터를

백업하게 되지요. 따라서.. 09시의 로그 백업은? 08시~09시 까지의 데이터 변경만을

백업하게 되며.. 10시의 로그 백업본은 09시~10시 사이의 데이터 변경만을.. 식으로

백업을 하게 됩니다.

하지만 차등 백업은?

이러한 방식으로.. 가장 최근의 풀백업본 부터 전체 변경을 포함하게 됩니다.

즉! 09시의 차등 백업본은? 08시~09시 까지의 데이터 변경을 가지게 되며

12시의 차등 백업본은? 08시~12시 까지의 데이터 변경을 가지게 됩니다.

로그를 중복해서 가지게 된다는 의미 이지요.

하지만 중복보다도.. 어느정도의 복구 속도를 높일 수 있으며..

SQL2000부터는 비트맵 백업 방식이라고 해서 대단히 빠르게 백업이 가능하다는 장점이

있게 되었습니다. 하지만 역시 개인적으로는 풀백업 + 로그 백업을 선호하며

여러 장점이 있기 때문에 더 선호합니다.

차등 백업일 경우 역시나 12시 30분에 데이터베이스가 깨졌다고 생각해 보도록

하지요.

이럴 경우 어떻게 복구를 하면 될까요?

08시의 풀백업본을 리스토어 합니다.

이어서!!!

12시의 차등 백업본 하나만!! 복구를 하면? 가능한 데이터의 복구가 된 것이겠지요.

자 세가지의 복구 방법을 차근차근 보신 겁니다.

이해가 되시나요?

그렇다면!!!

조금더 다른 이야기를 해 보도록 하지요.

복구 모델에 대한 이야기 입니다.

복구 모델

먼저 말그대로 단순한 단순 모델부터 보도록 하지요.

1. 단순 복구 모델

USE pubs

--pubsDB의복구모델을단순으로한다.

ALTER DATABASE pubs SET RECOVERY simple

--풀백업수행

BACKUP DATABASE pubs TO DISK = 'c:\pubsFull' WITH INIT

--데이터조회

SELECT TOP 1 title_id, price FROM titles

--조회된값은? - 꼭적으세요.

--데이터수정

UPDATE titles SET price = price * 2

--데이터조회- 조회된값꼭적으세요.

SELECT TOP 1 title_id, price FROM titles

--복구모델이단순일경우로그백업이가능한가?

BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--불가능 하다.

Msg 4208, Level 16, State 1, Line 1

복구모델이단순이면문BACKUP LOG을(를) 사용할수없습니다. BACKUP DATABASE를사용하거나ALTER DATABASE를사용하여복구모델을변경하십시오.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG이(가) 비정상적으로종료됩니다.

--SQL서버시스템종료SHUTDOWN

--탐색기등으로pubs의데이터파일인

--mdf 파일을지우자.

--C:\Program Files\Microsoft SQL Server\MSSQL\Data

--이기본경로이다.

--다시SQL서버를시작한다.

SQL서버를 시작하면 당연히 문제가 있습니다. PubsDB를 로드할 수 없다고 나오지요.

이런 식으로요..

pubs - 주의대상 - 바로 공포의 서스펙트 모드라고 하는 혼수상태 입니다.

이렇게 데이터베이스 이름이 나오는 이유는? 데이터베이스 이름과 같은 정보는

바로 MasterDB에 있기 때문입니다.

여하간 우리가 mdf 파일을 지웠으니 당연한가요? 이제 복구를 해 보도록 하지요.

--master DB 에서작업한다.

USE master

--복구를진행하자.

RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull.bak'

use pubs

--데이터조회

SELECT TOP 1 title_id, price FROM pubs..titles

자 복구를 하고 조회를 해 보았습니다. 값이 얼마가 나왔죠?

BU1032 19.9900

의 값이 나왔을 겁니다. 왜 그렇죠?

위에서 분명히 UPDATE를 한번 하지 않았나요?

하지만.. 우리가 UPDATE를 하기 전에 풀 백업을 진행 했지요?

아울러 로그 백업도 불가했고 데이터베이스가 깨진 이후에도 아무것도 할 수 없었기

때문에 오로지 가장 최근의 풀 백업본 까지만!!! 복구가 가능한 겁니다.

이게 바로 단순 모델입니다. - 물론 로그 백업 자체가 불가합니다.

하지만 자동으로 로그를 비워주니 로그가 한없이 커지는 사태는 발생하지 않지요.

다음으로 대량 로그 모델을 봐 보도록 할까요?

2. 대량 로그 모델

먼저 샘플텍스트화된 파일 을 다운로드 받으세요.

압축을 푸시면 titles.txt와 titles2.txt 파일이 있습니다.

아래 샘플에서 사용되니 이두 녀석을 c:\ 에 두시길 바랍니다.

--pubs DB를대량로그모델로바꾼다.

ALTER DATABASE pubs SET RECOVERY bulk_logged

--데이터베이스를풀백업한다.

BACKUP DATABASE pubs to DISK = 'c:\pubsFull' WITH INIT

--데이터를조회한다.

SELECT TOP 1 title_id, price FROM pubs..titles

--조회된값은?

--가격을*2 한다.

UPDATE pubs..titles SET price = price * 2

--데이터를조회한다.

SELECT TOP 1 title_id, price FROM pubs..titles

--조회된값은?

--BULK 작업을한다.

--다운로드받은titles.txt 파일을c:\에둔다. - SQL7에서

--BULK 작업은로그에남지않았으나SQL2000에서는남는다.

--일반적인INSERT, UPDATE, DELETE는기본적으로로그에남는다.

--그리고작업한다.

BULK INSERT pubs..titles FROM 'c:\titles.txt'

--데이터를조회한다.

SELECT top 2 title_id, price FROM pubs..titles

--Bulk INSERT로삽입된값은BU1001 에39.98이다.

--SQL7과틀리게sp_dboption없이BULK INSERT가가능하다.

--로그를백업하자. - BULK INSERT가로그에남을까남지않을까?

--SQL7까지는LOG에남지않는작업이었다.

BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--다시하나의데이터를BULK INSERT로넣자.

--지금넣는값은title_id가BU1002이며가격은23.9 이다.

BULK INSERT pubs..titles FROM 'c:\titles2.txt'

--데이터를조회해보자.

SELECT top 3 title_id, price FROM pubs..titles

--조회된값은?

자 이 샘플에서 언급하는 조회되는 값을 잘 적어 두시길 바랍니다.

그래야 어디까지 복구가 가능한지 알 수 있지요.

이제 복구 작업을 진행해 보도록 하지요.

--다시SQL서버를시작시키자.

USE master

--데이터가조회가능한가? - 불가하다.

SELECT TOP 3 title_id, price FROM pubs..titles

--데이터베이스를복구하자.

--현재풀백업본을복구하는것이다.

--이후LOG를복구할게더있으므로옵션으로

--WITH NORECOVERY를붙인다.

RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

WITH NORECOVERY

--로그를복구하자.

--이때마지막복구본이므로

--WITH RECOVERY 옵션을준다.

RESTORE LOG pubs FROM DISK = 'c:\pubsLog'

WITH RECOVERY

--데이터를조회해보자.

SELECT TOP 3 title_id, price FROM pubs..titles

데이터를 맨 마지막에 조회해 보니 어떤가요?

대량 로그 백업 샘플에서는 분명히 마지막에 어떤 값이었지요?

BU1001, BU1002, BU1032였을 겁니다. 분명히 LOG에 BULK INSERT의 데이터가 남았기

때문에 BU1001이 보이지요? 또한 두번째 BULK INSERT한 BU1002값은?

추후 로그를 백업하지 않았기 때문에 볼 수 없지요.

따라서.. 대량로드 작업에서는 가장 최근의 로그 백업본 까지만!!!

복구가 가능한 겁니다.

이제 전체 모델을 봐 보도록 하지요.

3. 전체 모델

--사용된데이터베이스를최초상태로복구합니다.

--맨처음단순모델할때사용했던백업본입니다.

RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

--다음풀모델을위해풀백업을진행합니다.

BACKUP DATABASE pubs TO DISK = 'c:\pubs1' WITH INIT

--데이터를조회해보면?

SELECT TOP 1 title_id, price FROM pubs..title

--조회된값은?

--price를*2 합니다.

UPDATE pubs..titles SET price = price * 2

--BULK INSERT로데이터를넣습니다. 값은BU1001입니다.

BULK INSERT pubs..titles FROM 'c:\titles.txt'

--데이터를조회하면?

SELECT TOP 2 title_id, price FROM pubs..titles

--조회된값은?

--로그를백업합니다.

BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--또다시BULK INSERT를합니다.

BULK INSERT pubs..titles FROM 'c:\titles2.txt'

--데이터를조회하면?

SELECT TOP 3 title_id, price FROM pubs..titles

--조회된값은?

--시스템셧다운

SHUTDOWN

--pubs의mdf 파일을삭제합니다. 단!

--ldf 파일은건드리지마세요.

mdf 파일을 삭제 했습니다. 조회된 값은 얼마죠?

BU1001, BU1002, BU1032일 겁니다. 그렇죠?

그럼 이제 복구를 해 볼까요?

--복구를진행합니다.

USE master

--데이터를조회가가능한가요? - 불가합니다.

SELECT TOP 2 title_id, price FROM pubs..titles

--로그를백업합니다.

--바로풀백업의중요한부분으로

--문제가발생해도로그가깨지지않았다면?

--로그가백업이됩니다.

--이때반드시WITH NO_TRUNCATE 옵션이필요합니다.

--INIT는초기화시켜백업하라는옵션이지요.

BACKUP LOG pubs TO DISK = 'c:\pubsLogNoTrunc' WITH NO_TRUNCATE, INIT

--로그백업이성공합니다.

--데이터베이스를복구해보도록하지요.

--복구할게더있으니NORECOVERY로합니다.

RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

WITH NORECOVERY

--로그를복구해보도록하지요.

--복구할게더있으니NORECOVERY로합니다.

RESTORE LOG pubs FROM DISK = 'c:\pubsLog'

WITH NORECOVERY

--로그를복구해보도록하지요.

--이로그는? pubs가깨진후로그만백업한것이죠?

--위에서옵션인WITH NO_TRUNCATE옵션으로백업한로그입니다.

--복구할게더이상없으니RECOVERY로합니다.

RESTORE LOG pubs FROM DISK = 'c:\pubsLogNoTrunc'

--데이터를조회해보면?

SELECT TOP 3 title_id, price FROM pubs..titles

--어디까지복구가되었나요?

자 여기까지 보셨으면 이제 다 보신 겁니다.

어떠세요? 데이터 백업 + 로그백업 이해 되시나요?

아울러 SQL2000의 새로운 복구 모델 세가지두 이해 되시구요?

그럼 이제 많이 받는 질문인.... 개발자나 관리자의 실수로..

WHERE절 없이 DELETE나 UPDATE를 쳐 버렸을때 복구하는 방법을 말씀 드리지요.

4. STOPAT을 이용한 복구

이때는 반드시 모델이 전체 모델 또는 대량 로그 이어야 합니다.

--데이터베이스를초기화시키기위해리스토어합니다.

RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

--데이터베이스를풀백업합니다.

BACKUP DATABASE pubs TO DISK = 'c:\pubs1' WITH INIT

--데이터를조회합니다.

SELECT TOP 1 title_id, price FROM pubs..titles

--초기화가잘되었는지확인하는겁니다.

--데이터를하나삽입합니다.

BULK INSERT pubs..titles FROM 'c:\titles.txt'

--데이터를조회하면?

SELECT TOP 2 title_id, price FROM pubs..titles

--조회된값은?

--로그를백업해보도록하지요.

BACKUP LOG pubs TO DISK = 'c:\pubsLog' WITH INIT

--역시나데이터를하나더넣도록합니다.

BULK INSERT pubs..titles FROM 'c:\titles2.txt'

--데이터를조회하면?

SELECT TOP 3 title_id, price FROM pubs..titles

--조회된값은?

--!!자이제문제상황입니다.

--개발자의실수로UPDATE를WHERE절없이쳐버렸습니다.

UPDATE pubs..titles SET price = 0

--컨트롤+ Z는아무리눌러도데이터는복구안됩니다.

SELECT title_id, price FROM pubs..titles

--바로문제가발생한시각을알아야만합니다.

SELECT GETDATE()

--결과시각은?

--코난이의경우아래와같은시각입니다.

2011-06-17 00:00:13

--어떻게해야할까요?

--1. 즉시로그를백업합니다.

--2. STOP AT 구문으로복구합니다.

--로그를백업합니다.

BACKUP LOG pubs TO DISK = 'c:\pubsLogNoTrunc' WITH NO_TRUNCATE, INIT

--데이터베이스를복구합니다.

--이때첫번째로그백업과.. 문제가발생한후로그백업사이로

--복구를진행해야합니다. 그렇지요?

--STOP AT을적절히사용하도록하지요.

--풀백업본을리스토어합니다.

RESTORE DATABASE pubs FROM DISK = 'c:\pubsFull'

WITH NORECOVERY

--첫번째로그백업본을복구합니다.

RESTORE LOG pubs FROM DISK = 'c:\pubsLog'

WITH NORECOVERY

--문제가발생한후로그를백업한것입니다.

--약간특이하게STOP AT이있습니다.

--시각지정은.. 문제가발생한시각- 1분정도로하겠습니다.

--2001-08-23 16:06:14.320 빼기1분

--그래야만.. 문제가발생하기바로전시각으로되돌릴수있겠지요?

--당연히문제가발생한후즉시알아야만데이터손실을그만큼줄일수있겠죠

--한일주일정도지난후문제가발생한걸알고.. 또한

--중요한회원데이터였다면? 생각하기도끔찍해지겠지요.

RESTORE LOG pubs FROM DISK = 'c:\pubsLogNoTrunc'

WITH STOPAT = '2011-06-17 00:00:13.320', RECOVERY

--데이터를조회해보면?

SELECT TOP 3 title_id, price FROM pubs..titles

--원하는데이터인BU1001, BU1002, BU1032가맞습니까?

어떠세요?

이제 STOPAT을 어떻게 사용하는지 감이 좀 잡히시나요? ^_^

자.. 그러면!!

이제 자동화를 이용해서 이러한 백업과 리스토어를 쉽게 하는 이야기를 조금

드려 보지요.

데이터베이스 유지관리 계획은.. 총체적으로 데이터베이스를 유지 관리하기 위한

기능입니다.

백업 / 인덱스 구축 등등의 관리자의 손이 개입되어야 하는 작업들을..

자동적으로 수행할 수 있게 도움을 주는 매우 유용한 기능입니다.

우리는 풀백업을 일정으로 만드는 방법에 대해서 한번 알아보도록 하겠습니다.

우선 SQL Server Agent 가 중지된 경우에는 아래와 같은 방법으로 실행합니다.

SQL Server Agent가 실행 중이면, 이렇게 해당 DB에서.. Maintenance Plan Wizard를 선택합니다.

Maintenance Plan Wizard를 실행하면 아래와 같은 그림이 나오는데 다음을 누릅시다.

여기서는 각각의 작업마다 스케줄을 나눠서 셋팅 하는 방법과

하나의 스케줄로 작업을 만드는 방법이 있습니다.

일단 저희는 아래거인 하나의 스케줄로 만들고 Change를 눌러서 일정을 선택합시다.

일정 선택 화면 입니다.

1. 스케줄 타입에는 총 4가지가 있습니다.

Start automatically when SQL Server Agent starts: SQL Server Agent가 시작했을 때 자동으로 수행

Start whenever the CPUs become idle: CPU가 유휴상태가 될 때마다 시작

Recurring: 반복 수행

One time: 한번

2. Frequency(빈도) 는 총 3가지가 있습니다. 일별, 주별, 월별입니다. 빈도에 따라서 설정할 수 있는 방법이 다양하게 나타납니다.

보통 풀 백업은 하루에 한번.. 트랜잭션 로그 백업은 30분 또는 1시간에 한번씩 백업 받는 것이 좋습니다. 예를 들어 새벽 3시에 풀 백업을 받고, 1시간 마다 트랜잭션 로그 백업을 하면서.. 오후 11시 데이터로 복원을 해야 한다면, 복원하는데 상당히 귀찮을 수 있습니다. 만약 복원을 자주 해야 한다면, 차등 백업도 함께 이용하는 것도 좋은 선택이라고 생각이 됩니다.

저희는 풀백업을 셋팅하는 것이기 때문에.. 스케쥴 타입은 Recurring(반복) 과 빈도는 일별을 선택하고 매일 오전 3시에 수행되도록 셋팅 합니다.

아래와 같이 설정이 되겠지요~!

일정을 셋팅한 뒤에 OK를 눌러서 창을 닫습니다.

그리고 Next 를 누르면 아래와 같은 화면이 나타나게 됩니다.

저희가 하고자 하는 것은 풀 백업이기 때문에 Back up Database(Full)을 선택을 하고Next를 선택합시다.

이번 화면은 실행 순서를 정하는 화면입니다. 만약 이전 창에서 여러 개를 선택한 경우, 여기서 실행 순서를 결정할 수 있습니다.

저희는 하나 밖에 없기 때문에 바로 Next를 누릅시다.

이번 화면에서 상단에 Database(s)를 누르면 내가 백업하고 싶은 Database를 선택할 수 있습니다.

그리고 나서 Backup set will expire를 통해 특정 일자 후에 자동으로 삭제할 수 있도록 설정할 수 있습니다.

그리고 저희는 Back up to 에 disk 그리고 Create a backup file for every database 를 선택하고, Create a sub-directory for each database(각각의 데이터베이스를 위한 서브 폴더 생성)을 체크하고 백업할 디렉토리를 선택합시다.

아래와 같은 그림이 되겠죠~!

그리고 나서 Next 를 누릅시다.

이번에는 백업된 결과를 Text 또는 이메일을 통해 받을 수 있는 부분을 셋팅 합니다.

Next를 누릅시다.

마지막으로 Finish를 누릅시다 ^^

위의 화면이 나오면 정상적으로 스케쥴이 생성된 것입니다.

복잡하면서도 간단한 백업 셋팅이 완료되었습니다.

트랜잭션 로그 백업 설정도 비슷하게 하실 수 있겠지요? ^^

감사합니다.

[출처] SQLER.COM http://www.sqler.com/395926

SQL2011(코드명 Denali) 시리즈 강좌 리스트
[SQL2011강좌] 1. 코드명 Denali 설치 방법
[SQL2011강좌] 2. SSMS 접속 및 간단한 쿼리 실행
[SQL2011강좌] 3. SQL Server 의 MDF, LDF 그리고 데이터베이스 생성하기
[SQL2011강좌] 4. DDL 1탄: 테이블 생성
[SQL2011강좌] 5. DDL 2탄: 쿼리를 이용한 테이블 컬럼 추가 및 삭제
[SQL2011강좌] 6. Primary key와 Unique 제약조건
[SQL2011강좌] 7. DML 1탄 INSERT
[SQL2011강좌] 8. DML 1탄 INSERT(identity 속성)
[SQL2011강좌] 9. Sequence
[SQL2011강좌] 10. pubs, northwind Database 예제 설치
[SQL2011강좌] 11. SELECT(단일 테이블)
[SQL2011강좌] 12. SELECT(JOIN)
[SQL2011강좌] 13. UPDATE, DELETE 절
[SQL2011강좌] 14. DISTINCT, UNION, UNION ALL 키워드
[SQL2011강좌] 15. ORDER BY, GROUP BY, HAVING
[SQL2011강좌] 16. SubQuery(서브쿼리)
[SQL2011강좌] 17. 뷰에 대한 이해 및 생성, 수정, 삭제
[SQL2011강좌] 18. 저장 프로시저에 대한 이해, 생성, 수정, 삭제
[SQL2011강좌] 19. 인덱스에 대한 이해
[SQL2011강좌] 20. 클러스터드 인덱스와 넌 클러스터드 인덱스
[SQL2011강좌] 21. 트랜잭션에 대한이해
[SQL2011강좌] 22. 트랜잭션의 격리수준 4가지
[SQL2011강좌] 23. 잠금에 대한 이해
[SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업)
[SQL2011강좌] 25. 백업과 복구 전략
[SQL2011강좌] 26. 커서란?

'프로그램&DB > MS-SQL' 카테고리의 다른 글

[MS-SQL2011강좌] 26. 커서(CURSOR) 란? by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업) by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 23. 잠금에 대한 이해 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 22. 트랜잭션의 격리수준 4가지 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 21. 트랜잭션에 대한이해 by 강동운님 (0)	2011.08.23

[MS-SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업) by 강동운님

프로그램&DB/MS-SQL 2011. 8. 23. 09:51 Posted by Josep.H.S

안녕하세요. 이스트럭(강동운) 입니다.

오늘은 SQL Server에서 백업과 복구에 대해서 설명드리겠습니다.

원문: http://www.sqler.com/127358

이번에 소개해 드릴 내용은 백업과 복구 입니다.

백업 / 복구라는 단어는 대부분의 이 글을 보시는 SQLER 님들은 한번씩

들어 보셨을 겁니다. 그렇다면 질문 입니다.

1. 백업과 복구를 주기적으로 하시는지요?

2. 사용자의 데이터베이스뿐 아니라.. MASTER, MSDB, MODEL 디비 역시

정기적으로 백업을 받으시는지요?

3. 복구 모델을 적절히 이해 하시는지요?

이에 대한 글입니다.

첫 번째 글인 1. 백업과 복구에 대한 이해는 기술적인 부분이 아닌 조금 더 상황적인

이야기를 진행하려 합니다. 천천히 이야기를 진행해 보도록 하지요.

백업이란 무엇인가?

백업은 간단히 데이터를 복사해 둔다는 의미 입니다.

물론 그냥 복사 하는게 아니라 정확히, 가능한 많은 데이터를 문제가 발생시 복구하기 위해

복사본을 만들어 두는 겁니다.

전체백업 = 풀백업과 같은말이며

차등백업 = 디퍼런셜 백업과 같은 말입니다. 혼동하지 마시구요..

SQL Server는 복구 모델이라고 해서 3가지 방식을 제공합니다.

1. 단순

2. 대량 로그

3. 최대

엔터프라이즈 관리자의 아무 데이터베이스에서 등록정보 -> 옵션 탭을 보시면

Full: 전체

Bulk-logged: 대량 로그

Simple: 단순

이러한 화면을 볼 수 있지요.

복구 모델은 데이터베이스를 특정 상태로 지정하는 것입니다.

내부적인 깊은 모델 비교는 다음에 기회가 생기면 해 보도록 하구요.

여기서는 그 기능성과 복구 가능 범위에 초점을 맞추도록 하겠습니다. 좀더 실질적인

이야기가 되겠지요.

약간 이야기를 건너서..

로그가 뭔가요? 제가 데이터베이스 강좌를 하면서 로그에 대해서 말씀 드렸지요.

로그는 데이터의 변경이 저장되는 곳입니다.

따라서 대부분의 경우 실제 데이터에 비해서 로그의 크기는 매우 작습니다.

데이터의 변경보다 대부분의 경우 데이터 조회가 훨씬 더 많으니까요.

일반적인 게시판을 생각해 보신다면 좋겠지요.

로그는 백업하면 비워집니다. - 지워진다기 보다는 비워진다는게 정확합니다.

껍데기는 그대로 이며 오로지 로그 데이터만이 비워지기 때문입니다.

만약 로그를 한번도 백업을 받지 않으셨다면?

다음 그림처럼.. 약간 이상한 상태를 보게 되지요.

대단히 흥미 있게도..데이터는 54메가 인데..

트랜잭션 로그는 한번도 백업을 받지 않았기 때문에 트랜잭션 로그 공간은

2.4 기가 가까이 됩니다. 로그는 매우 작은 분량임에도 불구하고 한번도 로그를

백업하지 않아 생기는 상황입니다. 사용되는 공간도 문제이지만 로그 데이터로 인해

시스템이 대단히 느려지는 사태 역시 발생할 수 있게 되지요.

만약 SQLER님들중 관리하고 계신 시스템이 있다면 이렇게 검사를

해 보시길 바랍니다. 위의 화면을 보시려면..

엔터프라이즈 관리자에서 해당 DB를 클릭하신후.. 왼쪽 맨위의 매뉴중

보기 -> 작업창을 보시면 위의 그림을 보실 수 있습니다.

위의 상황을 해결하시려면..

1.예를들어 pus 디비라면..

use pubs

DBCC SHRINKFILE (pubs_log, TRUNCATEONLY)

명령으로 로그를 비워낸후 불필요하게 커져버린 로그 화일의 크기를 줄일 수 있습니다.

2. 두번째로 코난이가 조금더 선호하는 확실한 방법으로..

--작업을수행하기전만약을위해풀백업을받는다.

--해당하는DB를detach로떼어낸다.

EXEC sp_detach_db 'pubs', 'true'

--잠시ldf 화일의이름을바꿔두자. - 만약을위해지우지않는다.

--mdf 화일만으로다시붙인다.

--이때ldf(로그화일)화일은최소크기인1M로재생성된다.

EXEC sp_attach_single_file_db 'pubs'

, 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

이런 방법도 있으니 참고 하시길 바랍니다.

다음으로 복구의 모델 세가지를 알아 보도록 합시다.

복구 가능 분량에 대해서 보는게 좋겠지요.

최대 모델 : 가장 많은 데이터를 복구할 수 있습니다.

실제 문제가 발생한 시각까지의 모든 데이터를 복구가 가능합니다.

대량로그 : 마지막 로그 백업 때까지만 복구가 가능합니다.

단순 : 이 모델로 지정시 로그 백업이 불가하므로 오로지 마지막 전체 백업시 까지만

복구가 가능합니다.

또한 종종접하는 문제 상황 발생시의 해결 방법을 알아 보도록 하지요.

Q. 현재 웹사이트를 개발하는 중입니다. 개발중이라 한번도 백업을

받은적이 없습니다. 개발자의 실수로 update 회원테이블 set 이름 = '아무개'

로 WHERE절 없이 update를 해 버렸습니다. 복구할 방법이 없을까요?

A. 복구는 불가합니다.

Q. 지속적으로 전체 백업만을 받아 왔습니다.

개발자의 실수로 update 회원테이블 set 이름 = '아무개'

로 WHERE절 없이 update를 해 버렸습니다. 복구할 방법이 없을까요?

A. 제약 조건이 있습니다. 1. 복구 모델이 전체 모델일것(DB를 생성하면 기본입니다.)

2. 문제 발생후 풀백업이나 로그에 쓰지 않는 작업을 하지 않았을것

3. 로그 백업이 가능할것 입니다.

만약 이 세 조건이 맞다면 바로 로그를 with no_truncate 옵션으로 백업하신후..

Stop at 명령으로 복구할 수 있습니다.

Q. 풀백업과 로그백업을 혼용해 지속적으로 백업하고 있습니다.

개발자의 실수로 update 회원테이블 set 이름 = '아무개'

로 WHERE절 없이 update를 해 버렸습니다. 복구할 방법이 없을까요?

A. 가능합니다.

1. 복구 모델이 전체 모델일것(DB를 생성하면 기본입니다.)

2. 문제 발생후 풀백업이나 로그에 쓰지 않는 작업을 하지 않았을것

이 두 조건이 맞다면 즉시 로그를 with no_truncate 옵션으로 백업하신후..

Stop at 명령으로 복구할 수 있습니다.

Q. 이전 서버에서 사용하던 시스템의 문제로 해당하는 데이터베이스의

mdf 화일과 ldf 화일만 남았습니다. 새로 설치하는 시스템에 복구가 가능할까요?

A. 이전 서버와 문자셋 정보(sp_helpsort 명령으로 보실수 있습니다.)가 같고

이전에 사용하던 SQL서버가 정상적인 상태로 종료 되었을때(엔터프라이즈 관리자에서

SQL서버를 스탑 시켰거나.. 정상적으로 윈도우 시스템을 종료시킨 경우 - 해당

mdf 화일과 ldf 화일이 정상적으로 닫혔을 경우) 복구가 가능하며 이때 사용하는 명령은

EXEC sp_attach_db 'pubs'

, 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

, 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

명령으로 복구할 수 있습니다.

Q. 이전 서버에서 사용하던 시스템의 문제로 해당하는 데이터베이스의

mdf 화일만 남았습니다. 새로 설치하는 시스템에 복구가 가능할까요?

A. 가능합니다.

이전 서버와 문자셋 정보(sp_helpsort 명령으로 보실수 있습니다.)가 같고

이전에 사용하던 SQL서버가 정상적인 상태로 종료 되었을때(엔터프라이즈 관리자에서

SQL서버를 스탑 시켰거나.. 정상적으로 윈도우 시스템을 종료시킨 경우 - 해당

mdf 화일이 정상적으로 닫혔을 경우) 복구가 가능하며 이때 사용하는 명령은

EXEC sp_attach_single_file_db 'pubs'

, 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

명령으로 복구하실 수 있습니다.

Q. 이전시스템에서 사용하던 풀 백업본이 있습니다.

새로 시스템을 설치한후 이 풀 백업본을 복구하려 하는데 방법이 없을까요?

A. 가능합니다.

약간 다른 방식으로 아래처럼 하심 됩니다.

1. 같은 이름의 db를 만든다 (텅빈 db)

2. 빈 db를 그냥 풀 백업 한다.

3. 원본서버에서 가져온 백업본으로 리스토어 한다.

4. 리스토어시 옵션의 강제로 덮어쓰기 하시면 됩니다.

의 순서로 진행하시면 무리없이 하실 수 있습니다.

자 이렇게 많이 받는 백업과 복구에 대한 질문을 들었습니다.

조금 귀찮을 수 도 있지만..

바로 뒤에서 자동화를 이용해 스케쥴로 SQL서버에 대한 백업을 진행할 수 있으니..

별로 신경 안쓰시고 모든 작업이 가능해 지실 겁니다.

그럼 다음 이야기 백업의 전략에서 실제 백업과 복구에 대해서 공부를 해 보도록

하지요.

감사합니다.

[출처] SQLER.COM http://www.sqler.com/394749

'프로그램&DB > MS-SQL' 카테고리의 다른 글

[MS-SQL2011강좌] 26. 커서(CURSOR) 란? by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 25. 백업과 복구 전략 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 23. 잠금에 대한 이해 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 22. 트랜잭션의 격리수준 4가지 by 강동운님 (0)	2011.08.23
[MS-SQL2011강좌] 21. 트랜잭션에 대한이해 by 강동운님 (0)	2011.08.23

«이전 1 다음»

NewsTwit

카테고리

Search

'트랜잭션 로그 백업'에 해당되는 글 2건

[MS-SQL2011강좌] 25. 백업과 복구 전략 by 강동운님

'프로그램&DB > MS-SQL' 카테고리의 다른 글

[MS-SQL2011강좌] 24. 백업과 복구에 대한 이해(풀백업, 차등백업, 트랜잭션 로그 백업) by 강동운님

'프로그램&DB > MS-SQL' 카테고리의 다른 글

tag cloud

공지사항

최근댓글

최근기사

글보관함

링크

티스토리툴바

« 2025/5 »
일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31