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

데이터베이스를 사용하다보면 잘못된 트랙젠션으로 인하여 그 이전의 상태로 복구해야 하는 필요성을 느끼게 되는 경우가 있습니다. 예를 들어 잘못해서 테이블을 전부 지워버린 경우 그 이전 상태로 복구하거나 아니면 수작업으로 입력해야 합니다.

로그 백업을 이용하여 특정 시점으로 데이터베이스를 복구하는 방법을 살펴보도록 하겠습니다.


가끔 이런 질문을 받습니다.

"큰일 났어요. 잘못해서 테이블을 다 지워버렸거든요. 이거 복구하지 못하면 저 짤려요. 제발 살려주세요.."

저도 위와 같은 경우를 경험한 적이 있었습니다. 분명히 SELECT를 하려는 생각으로 쿼리문을 날렸는데 아뿔사, 제가 수행한 쿼리문은 SELECT 문이 아니고 DELETE 문이었습니다. 참으로 황당하더군요.

SQL 서버에서는 트랜잭션 로그 백업을 이용하여 특정 시점으로 데이터베이스를 복구하는 방법이 있습니다. 위와 같이 테이블을 전부 지워버렸으면 지우기 바로 이전 상태로 복구하면 모든 문제는 해결이 됩니다. 물론 이와 같은 복구 작업이 항상 가능한 것은 아닙니다. 다음과 같은 조건이 만족된 상태여야 합니다.

① 데이터베이스 백업모델이 "최대" 여야 합니다.
② 전체 백업을 받은 상태여야 합니다.
③ 이전의 로그백업이 있다면 안전하게 보관된 상태여야 합니다.

지금 부터의 복구 예제는 다음과 같은 순서로 이루어 집니다.

o 예제 테이블 만들기
o 테이블에 5개의 레코드 추가
o 데이터베이스 전체 백업
o 테이블에 5개의 레코드 추가
o 데이터베이스 로그 백업
o 테이블에 5개의 레코드 추가
o 테이블 삭제 (문제발생)
o 현 시점의 로그 백업
o 전체백업 Restore
o 기존 로그 백업 Restore
o 문제 발생후 백업 받은 로그 Restore(테이블을 지우기 전까지만)

단, 예제에 사용되는 sqlworld 데이터베이스의 복구모델은 "최대" 입니다.

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

우선 Test1 이라는 예제 테이블을 만들고 레코드 5개를 추가하도록 하겠습니다.


CREATE TABLE Test1
(
col1 int,
col2 char(05)
)
GO
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')


이제 지금 상태의 sqlworld 데이터베이스를 전체 백업 받도록 하겠습니다.


BACKUP DATABASE sqlworld TO DISK = 'D:\sqlworld.bak'

위 전체 백업으로 인해 우리는 어떤 사태가 발생하더라도 Test1 테이블에 5개의 레코드가 추가된 상태로 복구를 할 수 있습니다.(당연하죠)

2) 로그백업

이제Test1 테이블에 레코드를 5개 더 추가하도록 하겠습니다.


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')

앞에서 전체 백업을 받은 상태이므로 이제는 방금 작업한(5개의 레코드 추가) 트랜잭션만을 보관하도록 로그 백업을 받도록 하겠습니다. (물론 지금 같은 경우는 예제이므로 데이터량이 많지 않아 전체 백업을 다시 받아도 되지만 실무에서는 전체 데이터베이스 양이 엄청 큰 경우라면 로그 백업을 받는게 더 효율적일 수 있습니다.)


BACKUP LOG sqlworld TO DIsk = 'D:\sqlworld_log.bak'

만일 이 상태에서 데이터베이스에 문제가 발생하여 복구를 해야 하는 경우는 처음에 받은 전체 백업을 Restore 한 후에 뒤에 받은 로그 백업을 Restore 하면 됩니다. 여기 까지는 누구나 다 아는 사실입니다.

3) 사태발생

이제Test1 테이블에 레코드를 5개 더 추가하도록 하겠습니다.


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

자! 지금까지는 일반적인 작업이 순조롭게 진행이 되고 있는 상태임을 볼 수 있습니다.

그런데 제가 잘못해서 이 상태에서 다음과 같은 퀴리문을 이용하여 Test1 테이블을 전부 삭제 해 버렸습니다. 이 때의 시간이 2001-11-19 1:10AM 입니다.


DELETE FROM Test1

큰일 났습니다!
엄청난(?) 양의 Test1 테이블이 전부 지워져 버렸습니다!

4) 복구하기

이제 해야 할 일은 Test1 테이블을 지우기전 즉 15개의 레코드가 존재하는 상태로 복구하는 것입니다. 다음의 순서를 정확히 기억하시기 바랍니다.

1) 현재 시점의 로그를 NO_TRUNCATE 옵션을 이용하여 백업받기

다음과 같이 NO_TRUNCATE 옵션을 이용하여 현재 상태의 로그를 백업 받습니다.(실무에서는 "모두 작업을 멈추시오!" 라고 외치고 백업 받아야 합니다)


BACKUP LOG sqlworld TO DISK = 'D:\Check.bak' WITH NO_TRUNCATE

2) 전체 백업 Restore

우선 제일 먼저 백업 받은 전체 백업을 Restore해야 합니다. 단 계속해서 추가적인 Restore 작업이 수행되어야 하므로 WITH NORECOVERY 옵션을 이용해야 합니다.


RESTORE DATABASE sqlworld FROM DISK = 'D:\sqlworld.bak' WITH NORECOVERY

이제 처음 레코드 5개가 추가된 상태로는 복구가 되었습니다. 하지만 WITH NORECOVERY를 사용했으므로 아직은 접근할 수 없는 상태입니다.

3) 로그 백업 Restore

전체 백업 후에 5개의 레코드를 추가하고 백업받은 로그가 있습니다. 이것을 WITH NORECOVERY 옵션으로 계속해서 Restore 해야 합니다.


RESTORE LOG sqlworld FROM DISK = 'D:\sqlworld_log.bak' WITH NORECOVERY

이제 처음 레코드 5개를 포함해서 새로 추가된 5개의 레코드 까지는 복구가 된 셈입니다. 하지만 WITH NORECOVERY를 사용했으므로 여전히 접근할 수 없는 상태입니다

4) STOPAT을 이용한 특정 시점까지의 로그 백업 Restore

문제 발생후 백업받은 로그 안에는 새롭게 추가된 5개의 레코드에 대한 작업과 함께 테이블 전체를 지운 작업이 포함되어 있습니다. 여기서 우리는 테이블 전체를 지운 작업 바로 이전까지만 Restore를 하면 됩니다. 이때 사용되는 것이 STOPAT 이라는 옵션입니다.

이 옵션을 이용하여 다음과 같이 테이블 전체를 지웠던 2001-11-19 1:10AM 이전 까지만 Restore 하면 됩니다. 그래서 2001-11-19 01:09 까지만 Restore 하겠습니다.


RESTORE LOG sqlworld FROM DISK = 'D:\Check.bak'
WITH RECOVERY, STOPAT = '2001-11-19 01:09'

그리고 WITH RECOVERY 옵션을 사용한 이유는 Restore가 다 끝났으므로 사용자가 접근 할 수 있게 하기 위함 입니다.

이렇게 해서 우리는 완벽하게 테이블이 지워지기 바로 전까지를 복구 할 수 있었습니다.

5) 정리

위 내용을 직접 수행해서 결과를 확인해보시기 바랍니다. 그렇지 않고 실제 문제가 발생한 경우에 복구 작업을 하려고 하면 안절부절 하게되기 때문입니다. 많은 분들이 백업은 받을 줄 아는데 정작 중요한 복구는 못하는 경우가 있습니다.

물론 복구할 일이 없는데 제일 좋지만 만일의 경우에 대비해서 연습해 두는 것이 좋습니다. 그리고 백업받은 데이터는 안전하게 보관을 하고 있어야 합니다. 아무리 복구 방법을 잘 안다고 해도 백업받은 데이터가 손상이 된 상태라면 아무 의미가 없기 때문입니다.

출처 : http://www.taeyo.pe.kr

★가정을 하자!!

하야시의 컴퓨터는 다음과 같다.

컴퓨터이름 : waglnet

IP :123.123.123.123 (<--IP가 참 특이하죠 ^^; )

OS : WIN2000

DATABASE : SQL2000

DB : blood (sql서버안의 계정. master, model, pubs...등과같은)

SQL ODBC연결명 : bloodwar

사용자 계정 : hahaysh

비밀번호 : 123

CNU(클라이언트유틸러티셋팅) : starcraft

엑세스 파일명 : asp.mdb

엑세스파일경로 : c:\asp\asp.mdb

엑세스파일 ODBC연결명 : asp




▷ SQL서버 : ODBC 연결방법

이방법은 ODBC 설정없이도 ODBC를 통한 접근을 가능하게 만들어준다.

Driver= {SQL Server}; Server=(컴퓨터이름)혹은(IP)혹은(CNU셋팅명);Database=디비이름; UID=아이디; PWD=비밀번호

※위의 CNU는 클라이언트 네트워크 유틸리티의 약자이다.

예)

첫 번째 : ODBC이용

Driver={SQL Server}; Server=bloodwar;Database=blood; UID=hahaysh; PWD=123

두 번째 : 컴퓨터이름이용

Driver={SQL Server}; Server=waglnet;Database=blood; UID=hahaysh; PWD=123

세 번째 : IP이용

Driver={SQL Server}; Server=123.123.123.123;Database=blood; UID=hahaysh; PWD=123

네 번째 : CNU셋팅이용

Driver={SQL Server}; Server=starcraft;Database=blood; UID=hahaysh; PWD=123

예제:

아래의 예제가 실제 사용할 수 있는 예제인데... 우리는 디비연결하는 부분만 알아보기로 했기 때문에... 잡다한(?)것들은 뺀상태로 디비연결하는 부분만 따로 그밑에 소스에서 정리를 하였다. 혼동하지 않도록 하자.




<% '이소스가 실제를 쓰는 소스일 것이다.
db.open "Driver={SQL Server}; Server=waglnet;Database=blood; UID=zerg; PWD=zerg"
function nullcheck(a)
if trim(a)="" then
%>

<% response.end
else
nullcheck=replace(trim(a),"'","''")
end if
end function
function htmlcheck(b)
htmlcheck=server.htmlencode(b)
end function
%>


원래 하야시는 디비연결을 해주는 인클루드파일에 recordset개체와 command개체를 생성시켜두는 방법을 쓴다. 하지만 밑의 예제에서 뺐다. 이점도 유의 하기 바란다.

DNS이용 ODBC연결법 첫번째



<% 'ODBC설정이 선수되어야 한다. ODBC연결명으로

db.open "dsn=bloodwar;uid=hahaysh;pwd=123"

%>


DNS없이 ODBC연결법 두번째



<% 'ODBC설정이 필요없다. IP로

db.open "Driver={SQL Server}; Server=123.123.123.123;Database=blood; UID=hahaysh; PWD=123"

%>


DNS없이 ODBC연결법 세번째



<% 'ODBC설정이 필요없다. 컴퓨터이름으로

db.open "Driver={SQL Server}; Server=waglnet;Database=blood; UID=hahaysh; PWD=123"

%>


DNS없이 ODBC연결법 네번째



<% 'ODBC설정이 필요없다. CNU설정으로

db.open "Driver={SQL Server}; Server=starcraft;Database=blood; UID=hahaysh; PWD=123"

%>






▷ SQL서버 : OLE DB 접근 방법

Provider=SQLOLEDB; Data Source=컴퓨터이름혹은IP혹은CNU설정명;Initial Catalog= ;디비이름; User id= 아이디;password= 비밀번호

예:

첫 번째: 컴퓨터이름으로

Provider= SQLOLEDB;Data Source=waglnet;Initial Catalog= blood;User id= hahaysh; password=123

두 번째: IP이름으로

Provider= SQLOLEDB;Data Source=123.123.123.123;Initial Catalog= blood;User id= hahaysh; password=123

세 번째: CNU셋팅으로

Provider= SQLOLEDB;Data Source=starcraft;Initial Catalog= blood;User id= hahaysh; password=123



OLE DB 직접 접근 방법 첫번째



<% '컴퓨터이름으로

db.open "Provider= SQLOLEDB;Data Source=waglnet;”&_
“Initial Catalog= blood;User id= hahaysh; password=123"

%>


OLE DB 직접 접근 방법 두번째



<% ' IP로

db.open "Provider= SQLOLEDB;Data Source=123.123.123.123;”&_
”Initial Catalog= blood;User id= hahaysh; password=123"

%>


OLE DB 직접 접근 방법 세번째



<% 'CNU연결명으로

db.open "Provider= SQLOLEDB;Data Source=starcraft;”&_
”Initial Catalog= blood;User id= hahaysh; password=123"

%>




▷ Ms-Access OLE DB 직접접근 방법

Provider=Microsoft.Jet.OLEDB.4.0;Data Source= 엑세스파일의 물리경로

예:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\asp\asp.mdb

OLE DB 직접 접근 방법



<% 'CNU연결명으로

db.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\asp\asp.mdb"

%>




위의 내용을 하나의 파일로 정리를 해보았다. 참고하도록 하자.

별로 의미가 없는거기는 하지만... 한번 봐두록 하자.

도움이 되리라 생각을 한다.



<%

밑에꺼 중에서 하나를 쓰면 될것이다.!!

db.open "dsn=bloodwar;uid=hahaysh;pwd=123"

db.open "Driver={SQL Server}; Server=123.123.123.123;Database=blood; UID=hahaysh; PWD=123"

db.open "Driver={SQL Server}; Server=waglnet;Database=blood; UID=hahaysh; PWD=123"

db.open "Driver={SQL Server}; Server=starcraft;Database=blood; UID=hahaysh; PWD=123"

db.open "Provider=SQLOLEDB;Data Source=waglnet;Initial Catalog=blood;User id=hahaysh; password=123"

db.open "Provider=SQLOLEDB;Data Source=123.123.123.123;Initial Catalog=blood;User id=hahaysh; password=123"

db.open "Provider=SQLOLEDB;Data Source=starcraft;Initial Catalog=blood;User id=hahaysh; password=123"

db.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\asp\asp.mdb

db.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\asp\asp.mdb"

%>




※아래의 OLEDB에대해 너무나 깜찍하게 정리를 해주신 태오님의 멘트를 같이 올린다.

더욱 자세한 사항은 태오님의 사이트를 방문해서 자세히 보도록 하자.

그럴리는 없겠지만, 혹시나해서 태오님의 사이트주소를 같이 올린다.

자주 들러 태오님의 내공을 흡입해보도록 하자.!!

http://www.taeyo.pe.kr

잠깐 !! 잘 모르겠는디요. ADO, ODBC, OLEDB 뭐가 무엇을 하는것이죠?

그렇습니다. 분명 혼란함을 느끼시는 분들이 있을 것입니다.
이 즈음에서 간단하게만 정리하고 계속나아가고자 하는데요.
ADO는 데이터를 다루는 개체이며, ODBC, OLEDB는 데이터의 제공자격입니다.
무슨 말인지 잘 이해가 안 된다면 다음 예를 보도록 하세요. (이것은 단지 예일 뿐이기는 합니다)

우리에게는 동시통역기가 하나있다고 가정해 봅시다요.
이를 동작시키면 외국말들이 자동으로 알아들을 수 있게 나온다고 가정합니다.
또한, 우리가 이야기하면 자동으로 번역해서 말을 전해주기도 하고 말이죠.
대신에 그러한 기능을 사용하기 위해서는 그 통역기에는 통역하고픈 각각의 나라용 칩을
기계에 추가해 주어야 합니다.

기본적으로 미국, 러시아, 일본의 칩은 제공된다고 가정합시다.
이제 외국사람들을 만나는 것은 겁나지 않을 것입니다.
만나면 즉시 이 자동통역기를 동작시키면 되니 말이죠. 하하.. 이젠 외제를 만나도 걱정없을 검다...
그런데, 갑작스럽게 남아프리카 사람을 만나게 되었습니다. 이 때도 걱정이 없슴다.
남아프리카용 통역 칩을 사서 기계에 장착하면 되니 말이죠. 돈만 있으면... 하하

이 동시통역기가 바로 ADO라고 볼 수 있으며, 각 나라용 칩은 ODBC 라고 볼 수 있습니다.
그리고 각각의 외국인들은 데이터베이스들이라고 볼 수 있고 말이죠. 이해가 가시죠?

그런 어느날, 기존의 통역기능을 좀 더 효과적으로 사용할 수 있으면서도,
동물의 언어까지도 통역이 가능한 기술이 개발되었으니 그것이 바로 OLEDB 기술이라
하더라는 말씀입니다.

주의할 것은 이렇게 원본 데이터를 우리가 사용할 수 있게 제공해주는 역할을 하는 것이
ODBC, OLEDB 기술이구요. 이렇게 제공받은 데이터를 처리하는 기술이 ADO라는 것.

어때요? 쏙쏙 들어오죠???

출처 : http://www.sqlservercentral.com/

내용 : 10 Steps to Securing your SQL Server

SQL서버의 보안을 높이기 위한 10단계의 글이며 요약 내용은 다음과 같습니다.


가능한 윈도우 인증을 사용한다.

sa계정 사용을 주의한다.

BUILTIN/Administrators 그룹을 제거한다.

서비스 시작 계정을 변경한다.

실패한 로그인과 거부된 로그인을 감사한다.

최신 서비스팩과 핫픽스를 적용하고 유지한다.

확장 저장 프로시져를 보호한다.

SQL서버 포트를 변경하고 보호한다.

저장 프로시져를 통한 접근을 제어한다.

OS를 보호한다.