제 3회 영남지역 Tour 기획세미나 SQL Server 효율적인 백업&복원 성공전략 이렇게 하자!!

Presentation on theme: "제 3회 영남지역 Tour 기획세미나 SQL Server 효율적인 백업&복원 성공전략 이렇게 하자!!"— Presentation transcript:

1 제 3회 영남지역 Tour 기획세미나 SQL Server 효율적인 백업&복원 성공전략 이렇게 하자!!
MCT/MCDBA/MCSE

2 차례 규모별 & 중요도별 데이터베이스 백업복원 전략 ( 2:00 ~ 3:00 )
Break time ( 3:00 ~ 3:10 ) 백업및 복원 진행중 문제 상황 발생시 대처방법 (1부) ( 3:10 ~ 4:10 )

3 차례(계속) Break Time ( 4:10 ~ 4:30 ) 백업및 복원 진행중 문제 상황 발생시 대처방법 (2부)
( 4:30 ~ 5:30 )

4 규모별 & 중요도별 데이터베이스 백업복원전략
규모별 & 중요도별 데이터베이스 백업복원전략 데이터의 규모와 중요도를 기준으로 간단한 백업 복원 시나리오와 대규모 또한 중요한 데이터의 안전한 백업,복원 및 복구 전략을 소개합니다

5 세션1 - 차례 백업은 왜? 데이터 손실의 주범은? 백업 전략을 세우는 기준은? 데이터가 손실되게하는 손상
가용성 요구사항 분석외 데이터가 손실되게하는 손상 시나리오1 – 소규모 데이터베이스의 백업 복원 (上,中) 시나리오2 – 대규모 데이터베이스의 백업 복원 (中,下) 백업에 관한 11가지 팁 시스템 데이터베이스 백업복원

6 백업은 왜? 데이터 손실의 주범은? Q : 백업은 왜 하는가? Q : 데이터 손실의 주범은 누구?
데이터의 손실을 막기 위해 백업을 합니다. Q : 데이터 손실의 주범은 누구? 미숙련 사용자의 실수 적절한 권한만을 사용자에게 할당한다 (security.sql ) 책임자가 즉각적인 대응조치를 할 수 있도록 해둔다 ( LogExplorer 시연 ) 재해 백업은 여러곳에 보관합니다 도난 보관시 암호를 걸어둡니다. ( pwdbackup.sql )

7 백업 전략을 세우는 기준은? 1. 자금 2. 데이터의 중요도 수준 3. 허가된 다운타임
- TCO(Total Cost Ownership) 기준에 맞춘다

8 가용성 요구사항 분석 가용성 요구 사항이 무엇입니까? ( 하루 중 언제 데이터베이스를 온라인시켜야 합니까? )
업무에 시스템 작동 중지 시간의 재정 비용은 얼마나 됩니까? 미디어 오류를 경험한다면 허용할 수 있는 시스템 작동 중지시간은? 재해의 경우 허용할 수 있는 시스템 작동 중지 시간은 얼마나 됩니까? 중요도는 어느 정도입니까? 손실된 데이터를 다시 만드는 것이 얼마나 간단합니까? 조직에 시스템 및 데이터베이스 관리자가 있습니까?

9 데이터가 손실되게하는 리소스에러들 하드웨어 손상 운영체제 손상 SQL서버 인스턴스 손상 데이터베이스 손상

10 시나리오 – 소규모DB의 백업 복원 데이터베이스 관리자는 크기 100MB정도의 데이터베이스를 관리하고 있으며 이 데이터베이스는 장차 200MB정도까지 커질 것으로 예상됩니다 일반적으로 업무 시간인 오전 8:00 에서 오후 10:00까지 주로 사용됩니다. 데이터베이스 관리자는 어떤 불특정 사용자의 실수에서 데이터 보호 필요성을 실감했습니다 그러나 회사의 넉넉하지 못한 예산때문에 장비는 기존 하드디스크 하나만을 가지고 있는 상황입니다.

11 시나리오 – 백업 복원(上) 정기적으로 전체백업만을 수행하는 방식 Demo : fullbackup.sql Data Log

12 상황 – 전체백업만을 행하는 경우(上) 상황은 다음과 같다. 데이터베이스는 기본형으로 아무런 조치를 따로 취하지 않아 단일 MDF,LDF로 이뤄저 있다 1.월요일 새벽 2시 전체 백업 2.화요일 새벽 2시 전체 백업 3.수요일 새벽 2시 전체 백업 4.목요일 새벽 2시 전체 백업 5.금요일 오후 4시 시스템 중단

13 경고 – 전체백업만을 수행하는 경우 전체백업을 수행하면 데이터와 로그는 완전히 다 백업이 되기는 한다. 그러나 로그가 무제한으로 증가하여 실제 데이터베이스 크기가 커지지 않더라도 매일 점차로 백업해야 되는 양은 늘어난다. 로그가 무제한으로 증가한다 위험상황발생시 로그백업 시간이 오래걸린다 마찬가지로 복구하는 시간 또한 역시 시간이 소요된다

14 결론 – 전체백업만을 수행하는 경우 시스템이 중단되기 전에 정기적으로 로그를 삭제해줘야 한다.
(예 : backup log 데이터베이스명 with no_log ) 또한 시스템이 중단되면 그 순간의 활성 로그를 백업해야 한다 (예 : backup log 데이터베이스명 to 백업위치 with no_truncate ) 활성 로그를 백업후 가장최근의 전체백업과 로그를 차례로 복구해야만 합니다

15 시나리오- 대규모 DB의 백업 복원 데이터베이스 관리자는 현재 크기 10GB정도의 데이터베이스를 관리하고 있으며 이 데이터베이스는 장차 20GB정도까지 커질 것으로 예상되고 빠르게 더 증가할 것으로 보입니다 관련 지침은 다음과 같습니다 (추가사항) 로그가 아주 많이 생성됩니다. 시간당 1기다일 경우도 있습니다.

16 시나리오 – 백업복원(中) 전체백업과 로그백업을 병행하여 수행하는 경우 데이터를 특정 위치까지 복원할 필요가 항상있습니다
fullandlog.sql Data Log Data Log Log Log Log Log Log Log

17 상황 – 전체,로그백업을 병행하는 경우 상황은 다음과 같다. 데이터베이스는 기본형으로 아무런 조치를 따로 취하지 않아 단일 MDF,LDF로 이뤄저 있는 경우를 산정합니다 1.월요일 새벽 2시 전체 백업 2.화요일 새벽 2시 로그 백업 3.수요일 새벽 2시 로그 백업 4.목요일 새벽 2시 로그 백업 5.금요일 오후 4시 시스템 중단

18 결론 – 전체,로그백업을 병행하는 경우 시스템이 중단되면 그 순간의 활성 로그를 백업해야 한다
(예 : backup log 데이터베이스명 to 백업위치 with no_truncate ) 활성 로그를 백업후 가장최근의 전체백업이후 로그를 차례로 복구해야만 한다 (차례로 복구하지 않을 경우 에러가 납니다)

19 시나리오 – 백업복원(下)  아주대규모로 변경사항도 많을경우 전체백업 증분백업 로그백업을 병행한다
fulldifferlog.sql Data Log  Log Log Log Log Log Log

20 상황 – 전체,증분,로그 백업을 할 경우 상황은 다음과 같다. 데이터베이스는 기본형으로 아무런 조치를 따로 취하지 않아 단일 MDF,LDF로 이뤄저 있다 또한 데이터 베이스 변경량은 엄청난데 반해 데이터베이스자체의 크기 변화량은 미미하다 1.월요일 새벽 2시 전체 백업 2.화요일 새벽 2시 로그 백업 3.수요일 새벽 2시 증분 백업 4.목요일 새벽 2시 로그 백업 5.금요일 오후 4시 시스템 중단

21 결론 – 전체,증분,로그 백업을 할 경우 시스템이 중단되면 그 순간의 활성 로그를 백업해야 한다
활성 로그를 백업후 가장최근의 전체백업 (전체백업과 증분백업사이의 로그는 복원 필요가 없다) ,증분백업, 그후의 로그들을 차례로 복원하면 된다

22 백업에 관한 11가지 팁(SQL매거진) 01. 기본 SQL서버 백업 소프트웨어를 사용해라.
02. 서드파티 제품을 사용하기 앞서 테스트 해라. 03. 백업계획을 수립해라. 04. 적절한 데이터베이스 복구모델을 선택해라. 05. 파일그룹 백업의 사용도 고려해라. 06. 적절한 경우 차등 백업을 고려해라. 07. 증분과 로그백업을 혼합해서 사용해라. 08. 부하테스트를 한 후 증분백업을 사용해라. 09. 파일로 먼저 백업한 후 테이프 백업을 고려해라. 10. 테이프로 직접백업한다면 다중백업장치를 고려해라. 11. 백업프로시저와 복구프로시저를 테스트해라.

23 시스템데이터베이스 distribution(서버를 복제배포자로 구성할 경우 내부생성) 시스템 데이터베이스 사용자 데이터베이스
master model tempdb msdb distribution pubs Northwind User1 사용자 데이터베이스

24 시스템 데이터베이스 master 데이터베이스는 SQL Server 시스템의 모든 시스템 수준 정보를 기록합니다. 이 데이터베이스는 모든 로그인 계정과 모든 시스템 구성 설정을 기록한다. master는 데이터베이스 파일의 위치를 포함하여 다른 모든 데이터베이스의 존재를 기록하는 데이터베이스이다. msdb 데이터베이스는 경고 및 작업을 예약하고 운영자를 기록하기 위해 SQL Server 에이전트에서 사용한다. model 데이터베이스는 시스템에서 만든 모든 데이터베이스에 대해 템플릿이다. tempdb는 모든 임시 테이블과 임시 저장 프로시저가 있다.또한 임시저장소 요구사항을 충족시켜준다

25 Break time ( 03:00 ~ 03:10 )

26 백업및복원 진행중 문제발생 시 대처법(上) 평상시 나타날 수 있는 일반적인 응급상황과 그 대처방법을 시나리오 형식으로 직접 구현한 후 해결할 것입니다. 상황별로 그 이유와 처리방법을 정확히 파악하여 당황하지 않도록 합시다

27 내용 데이터를 복구했을때 [로드하는 중] 으로 나올때 어떻게 처리하는가? 백업중에 에러가 발생할 경우 해결 방안은 ?
데이터 사용중에 [주의 대상]으로 나오는 경우 해결 방안은 ?

28 시나리오 – [로드하는중]으로 나온다 Q : 데이터베이스가 [로드하는 중]으로 나올경우는 어느 떄인가?
- [로드하는중]으로 나왔다는 것은 데이터베이스 복원 프로세스가 완료되지 않았다는 것을 말합니다. 추가로 남아있는 로그를 복원후 옵션에서 with recovery를 사용하면 됩니다 추가내용이 있을경우의 대처방법 추가내용이 없을경우의 대처방법 with_Recovery.sql

29 상황 – DB가 [로드하는중]으로 나온다

30 결론 - DB가 [로드하는중]으로 나온다 [로드하는중]은 다시말해 복구하다가 그만둔 아직 복구될것이 남았다는 뜻으로 이해하면 됩니다. 따라서 나머지 추가 로그등을 복구하던가. 지금상태에서 복구를 완료해서 데이터가 사용할 수 있게 해줘야 합니다.

31 시나리오 – 백업중에 에러가 날 경우 실제 데이터에 이상이 있을 수가 있다 DBCC 명령어들로 체크한다
과거 데이터 백업본으로 부터 복원을 시도해야한다 과거 데이터 백업본이 없는경우 Export를 사용 최대한 복구해야한다 backupError.sql

32 상황&결론- 백업중에 에러가 날 경우 백업중에 에러가난다. 이럴 경우 상황을 파악하기 위해 백업디바이스를 생성한 후에 다시한번 백업 시도를 해보면 쉽게 파악할 수 있다 (예) DBCC checkdb 정상 메세지가 아닌 일관성 오류가 난 테이블이 있는경우 이전 백업본으로 복원하던가 Export로 남은데이터를 최대한 살려야한다. 또는 LogExplorer로 보면서 복구할 수도 있습니다

33 시나리오 – 데이터베이스가 [주의대상] Q : 데이터베이스가 [주의대상]으로 나오는 경우는 어떠한 경우이며 그 경우 어떤 처리를 해야하는가? A : 데이터베이스 파일 (.MDF)가 손상을 입은 경우이다 로그만 백업하여 최종정보를 저장한후 복구 프로세스를 진행해야한다 Suspected.sql

34 결론 – 데이터베이스가 [주의대상] 시스템이 중단되면 그 순간의 활성 로그를 백업해야 합니다
활성 로그를 백업후 가장최근의 전체백업 (전체백업과 증분백업사이의 로그는 복원 필요가 없다) ,증분백업, 그후의 로그들을 차례로 복원하면 됩니다.

35 Break Time ( 04:10 ~ 04:30 )

36 백업및복원 진행중 문제발생 시 대처법(下) 앞의 과정에 이어진 내용입니다

37 시나리오 - 시스템 데이터베이스 위치? 특정 상황 발생시 시스템 데이터 베이스 또는 사용자 데이터 베이스의 위치를 바꾸는 경우가 왕왕 생길 수 있다. 그 경우 어떤 방법으로 해야만 하는가? movedb.sql Systemdb_backuprestore.sql

38 데모 user 데이터베이스의 이름 변경,이동 master 데이터베이스의 이동 model 데이터베이스의 이동
tempdb 데이터베이스의 이동 (팁) DB를 복구해달라는 요청이 들어오면 일단 다른이름으로 복구하고 정상이라면 이름을 원래데로 바꾸는 형식을 취하고있다

39 시나리오 – 시스템간 데이터베이스 복사 A시스템에서 B시스템으로 데이터베이스를 그대로 복사하고 싶습니다 가장 편리한 방법은?
DTS등을 이용한 방법도 있습니다.

40 데모 SQL서버를 중단시킨후 물리적으로 파일들을 복사하여 대상서버로 그대로 복사하는 방법이 그것이다.
DTS를 이용한 방법이 무난한 방법이지만 여기서는 편법을 소개하도록 한다. 데이터베이스 복사 마스터를 사용하는 법이 있습니다. 백업 복원을 사용할 수 있습니다.

41 시나리오 – 로그의 비정상적 크기 처리 로그가 비정상적으로 쌓여있는 데이터베이스를 볼수 있다(전체백업만 하는경우) 그럴때 로그 사이즈를 축소&조정하는 방법은? 또 여의치 않은 경우의 대처방법은? notcontroled.sql

42 데모 정상적인 방법 - DBCC shrinkfile 을 사용하여 로그사이즈 줄이기 시도
- 단독사용자 모드 설정후 위의 작업 재시도 - 로그사이즈를 더 크게 한 후 다시 시도

43 데모 앞서의 방법으로 여의치 않은경우 - 로그를 초기화 한다. 1.전체백업을 받는다
2.sp_detach_db를 사용 DB와 서버를 분리한다 3.로그파일의 이름을 변경한다 4.sp_atatch_single_file_db 5.sp_attachdb구문을 사용 하여 복원한다

44 감사합니다