이 내용은 2011년 6월 현재 SQL2011(코드명 Denali) Beta를 기준으로 작성 되었으며
SQL2011(코드명 Denali) 공식버전(RTM) 발표까지 꾸준히 업데이트 예정입니다.
아울러, 현재 Beta버전이지만 강좌를 따라해
보시는데 아무 문제 없으실거에요. 도움 되시길
바랍니다.
==================================================================================================
안녕하세요. 이스트럭(강동운) 입니다.
오늘은 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 go 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
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. 커서란?
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강좌] 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 |
