Step by Step: SQL Server에서 특정 시점으로 데이터 복원하기

기술 자료: 983139 - 이 문서가 적용되는 제품 보기.
모두 확대 | 모두 축소

소개

이 문서에서는 사용자 실수로 테이블 전체 데이터를 삭제했거나 잘못된 값으로 대량 업데이트가 발생 경우 데이터베이스 전체 백업 및 트랜잭션 로그 백업을 이용하여 삭제/변경 전의 데이터로 복구하는 방법에 대해 설명합니다.

아래 그림에서 ① 시점에 사용자 실수로 테이블 전체 데이터가 삭제되고 일정 시간이 지난 후(②시점) 오류를 발견했을 경우한, 사전에 받아 둔 전체 데이터베이스 백업(t1), 차등 데이터베이스 백업(t6), 그리고 이후 트랜잭션 로그 백업들(t7, t8, t9)로부터 장애 시점 직전으로 데이터를 복구할 수 있습니다.
그림 축소그림 확대
01


조건

지정된 시간으로 데이터를 복원하기 위해서는 해당 데이터베이스 복구 모델이 전체로 되어 있어야 합니다. 데이터베이스 복구 모델에 대한 내용은 다음 기술 문서를 참조하십시오.현재 데이터베이스의 복구 모델은 Microsoft SQL Server Management Studio(이하 SSMS)에서 해당 데이터베이스 속성 화면의 [옵션]에서 확인할 수 있습니다.
그림 축소그림 확대
02


장애 시나리오

특정 시점으로의 데이터베이스 복원 방법을 설명하기 전에 우선 다음 시나리오에 따라 장애 증상을 재현해 보았습니다. 재현 단계를 간단히 정리하면 다음과 같습니다.

초기 데이터

use MSTest
go

-- 데이터조회
select * from dbo.Employee

표 축소표 확대
empIDempNameSalary
100001Scott100,000
100002Evan90,000
100003Chris60,000
100004Eric30,000
100005Jason70,000

데이터베이스 전체 백업

  1. SSMS에서 해당 데이터베이스를 마우스 오른쪽 단추로 클릭합니다(여기서는 MX테스트를 예로 사용함). 그런 다음 [태스크], [백업]을 차례대로 클릭합니다.
    그림 축소그림 확대
    03


  2. 데이터베이스 화면에서 [일반]을 클릭한 다음 아래와 같이 지정합니다.
    • 데이터베이스(T): 백업 받으려는 데이터베이스
    • 백업 유형(K) 전체
    • 백업 구성 요소: 데이터베이스
    • 대상: 데이터베이스 백업 경로

    그림 축소그림 확대
    04


    그림 축소그림 확대
    팁
    데이터베이스 백업 경로를 변경하려면 기존 백업 대상을 제거한 후 [추가]를 클릭해 아래와 같이 새로운 경로를 지정하면 됩니다.
    그림 축소그림 확대
    05


  3. 데이터베이스 백업 화면에서 [확인]을 클릭합니다.
    그림 축소그림 확대
    06


임의의 트랜잭션 발생

데이터베이스 전체 백업 이후 쿼리창에서 dbo.Employee 테이블에 데이터를 추가합니다.

use MSTest
go

insert into dbo.Employee values('100006', 'John', '2006-12-10', 30000)
insert into dbo.Employee values('100007', 'Rob', '2007-04-19', 70000)

-- 데이터조회
select * from dbo.Employee

표 축소표 확대
empIDempNameSalary
100001Scott100,000
100002Evan90,000
100003Chris60,000
100004Eric30,000
100005Jason70,000
100006John30,000
100007Rob70,000

트랜잭션 로그 백업

데이터베이스 변경(가령, 데이터 INSERT/UPDATE/DELETE)이 일어나면 트랜잭션 로그 파일(확장자 LDF)에 이력이 기록됩니다. 따라서, 데이터베이스 전체 백업을 받은 이후 주기적으로 트랜잭션 로그 백업을 받으면 향후 데이터베이스를 복구할 때 가장 최근의 데이터까지 복원할 수 있습니다.

SSMS에서 트랜잭션 로그 백업을 받는 방법은 위의 데이터베이스 전체 백업 방법과 유사합니다.
데이터베이스 백업 화면에서 백업 유형을 트랜잭션 로그로 지정하면 됩니다.
그림 축소그림 확대
07


사용자 실수로 모든 Employee의 Salary가 수정됨

가령, 사용자가 쿼리창에서 WHERE 조건 없이 다음의 UPDATE문을 실행하게 되면 모든 Employee에 대한 Salary 값이 0으로 변경됩니다.

--장애발생시간
select GETDATE() -- 2010-04-09 14:19:12.787


use MSTest
go


-- 사용자 실수로 전체테이블에 대한salary 값을 0으로변경
update dbo.Employee
set salary = 0

-- 데이터조회
select * from dbo.Employee

표 축소표 확대
empIDempNameSalary
100001Scott0
100002Evan0
100003Chris0
100004Eric0
100005Jason0
100006John0
100007Rob0

해결 방법

아래 단계로 위 시나리오에서 잘못 수정된 Salary Data를 UPDATE 이전으로 복구하는 방법에 대해 설명합니다.

1단계: 트랜잭션 로그 백업

앞서 설명한 것처럼 데이터베이스 변경 내역은 모두 트랜잭션 로그 파일에 기록됩니다. 위 시나리오에서 전체 테이블에 대한 UPDATE 작업 역시 트랜잭션 로그에 기록되므로 이 시점의 트랜잭션 로그 백업을 받아야 합니다.

그림 축소그림 확대
08


탐색기에서 살펴보면 다음의 세 데이터베이스 백업이 존재함을 확인합니다.
그림 축소그림 확대
09

  • MSTest.bak : 데이터베이스 전체 백업
  • MSTest_Log.trn : 첫번째 트랜잭션 로그 백업
  • MSTest_Log2.trn : 두번째 트랜잭션 로그 백업 (전체 테이블 Salary Data가 수정된 기록 포함)

2단계: 데이터베이스 전체 백업(MSTest.bak)을 다른 데이터베이스 이름으로 복원함

동일 데이터베이스 이름으로 지정해 기존 데이터베이스를 Overwrite할 수 있으나 일반적으로 장애 이후 추가된 데이터가 존재할 것이므로 신규 데이터베이스로 복원한 후 장애 이전으로 정상적으로 복구되었는지 확인하는 것이 좋습니다.
  1. SSMS에서 해당 데이터베이스를 마우스 오른쪽 단추로 클릭합니다(여기서는 MSTest를 예로 사용함)그런 다음 [태스크], [복원], [데이터베이스]를 차례대로 클릭합니다.
    그림 축소그림 확대
    10


  2. 데이터베이스 복원 화면에서 복원 대상 아래의 데이터베이스에 신규 데이터베이스명을 지정합니다(여기서는 MSTest_NEW를 예로 사용함).
    그림 축소그림 확대
    11


    위 화면의 복원에 사용할 백업 세트 선택 목록을 보면 자동으로 세개의 데이터베이스 백업 파일이 나타남을 확인할 수 있습니다. 이때, 장애 시점의 트랜잭션 로그 백업이 가장 마지막에 두셔야 합니다. 만일, 장애 시점의 트랜잭션 로그 백업 이후에 받은 트랜잭션 로그 파일이 존재할 경우 이는 uncheck합니다.
  3. 데이터베이스 복원 화면의 지정 시간을 지정하기 위해
    그림 축소그림 확대
    icon0028
    을 클릭합니다.
  4. 지정 시간 복원 화면에서 장애 발생 직전의 시간으로 지정해 줍니다.
    위 시나리오에서 장애가 발생 직전에 기록해 둔 시간으로 지정한 후 [확인]을 클릭합니다.
    select GETDATE()	-- 2010-04-09 14:19:12.787

    그림 축소그림 확대
    12


  5. 데이터베이스 복원 화면의 지정 시간이 변경됨을 확인합니다.
    그림 축소그림 확대
    13


    모든 설정이 끝난 후 데이터베이스 복원 화면의 [스크립트], [새 쿼리 창 동작 스크립팅]을 차례대로 클릭하면 RESTORE 명령에 대한 T-SQL문이 생성될 것입니다.
    그림 축소그림 확대
    14


    그림 축소그림 확대
    15


  6. 데이터베이스 복원 화면에서 [확인]을 클릭해 복원이 성공함을 확인합니다.
    그림 축소그림 확대
    16


3 단계: 데이터 복구 여부 확인

테이블의 Salary 데이터가 장애 이전으로 복구되었는지 확인합니다

use MSTest
go

select * from dbo.Employee

표 축소표 확대
empIDempNameSalary
100001Scott100000
100002Evan90000
100003Chris60000
100004Eric30000
100005Jason70000
100006John30000
100007Rob70000

참조

의견 보내기

표 축소표 확대
그림 축소그림 확대
의견 보내기

Microsoft 고객지원 사이트에서는 고객님의 소리를 귀담아 듣습니다. 아래 의견 보내기로 소중한 의견 보내주시기 바랍니다.

속성

기술 자료: 983139 - 마지막 검토: 2014년 5월 23일 금요일 - 수정: 1.4
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
키워드:?
kbstepbystep kbtshoot KB983139

피드백 보내기

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com