롤백 스크립트(ROLLBACK) & 되돌리기 전략 – MSSQL 실무

업무 중 UPDATE·DELETE 한 방에 잘못 날리면 식은땀이 나죠.
이 글은 즉시 복구부터 사후 복구까지, 상황별 되돌리기(rollback) 방법을 한 번에 정리합니다.

---

1) 트랜잭션 기본형 (실수 방지용 템플릿)

 

SET XACT_ABORT ON; -- 오류 시 자동 롤백(권장)

BEGIN TRAN;

-- ✅ 여기부터 작업
UPDATE dbo.Orders
SET    Status = 'Closed'
WHERE  OrderDate < '2025-01-01';
-- ✅ 여기까지 작업

-- 점검
SELECT @@ROWCOUNT AS AffectedRows;

-- 확인 후 커밋/롤백
-- COMMIT;
-- ROLLBACK;

• SET XACT_ABORT ON : 오류 한 번이면 자동 롤백(외래키/타임아웃 이슈에 특히 유용)

---

2) TRY…CATCH + XACT_STATE() 안전 롤백

 

BEGIN TRY
    BEGIN TRAN;

    -- 위험 작업
    DELETE FROM dbo.Logs WHERE LogDate < DATEADD(MONTH, -6, GETDATE());

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW; -- 오류 재발생(로그/모니터링)
END CATCH

 

---

3) SAVEPOINT로 “부분만” 되돌리기

 

BEGIN TRAN;

UPDATE dbo.A SET Qty = Qty - 10 WHERE Id = 1;
SAVE TRAN sv1;

UPDATE dbo.B SET Qty = Qty + 10 WHERE Id = 2;
-- 문제 발견!
ROLLBACK TRAN sv1; -- B만 되돌림

COMMIT; -- A는 반영

---

4) 되돌릴 수 있는 UPDATE/DELETE (OUTPUT 백업 패턴)

4-1) UPDATE 롤백 가능하게 하기

 

-- 1) 백업
SELECT d.*
INTO   dbo._bak_Product_20250922
FROM   dbo.Product d WITH (HOLDLOCK, UPDLOCK) -- 동시변경 방지
WHERE  Category = 'A';

-- 2) 변경 + 변경 전 값 저장
DECLARE @chg TABLE (PK int, OldPrice money);
UPDATE p
SET    Price = Price * 1.1
OUTPUT inserted.Id, deleted.Price INTO @chg(PK, OldPrice)
FROM   dbo.Product p
WHERE  Category = 'A';

-- 3) 문제 시 되돌리기
-- UPDATE p SET Price = c.OldPrice
-- FROM dbo.Product p JOIN @chg c ON p.Id = c.PK;

4-2) DELETE 롤백 가능하게 하기

 

-- 1) 삭제 대상 백업
SELECT d.*
INTO   dbo._bak_OrderDetail_20250922
FROM   dbo.OrderDetail d WITH (HOLDLOCK, UPDLOCK)
WHERE  OrderDate < '2024-01-01';

-- 2) 삭제
DELETE FROM dbo.OrderDetail
WHERE  OrderDate < '2024-01-01';

-- 3) 되돌리기(필요 시)
-- INSERT INTO dbo.OrderDetail ( ...컬럼목록... )
-- SELECT ... 동일 컬럼 ...
-- FROM dbo._bak_OrderDetail_20250922;

포인트

• OUTPUT/백업 테이블을 먼저 만들면 “수동 undo”가 쉬워집니다.
• 대용량은 TOP 10000 배치 + 야간 실행 추천.

---

5) INSERT 되돌리기(키 보관 후 삭제)

 

DECLARE @newKeys TABLE (PK int);

INSERT dbo.UserPoint(UserId, Point, Memo)
OUTPUT inserted.Id INTO @newKeys
SELECT u.Id, 100, 'event'
FROM dbo.Users u WHERE u.IsActive = 1;

-- 문제 시
-- DELETE p FROM dbo.UserPoint p JOIN @newKeys k ON p.Id = k.PK;

---

6) 스키마(DDL) 변경의 롤백

• DDL은 보통 즉시 커밋이라 실수 시 복구가 까다롭습니다.
• 원칙: DDL도 트랜잭션으로 감싸고, 변경 전 스크립트 준비.

 

BEGIN TRAN;

ALTER TABLE dbo.Customer ADD Phone2 VARCHAR(20) NULL;

-- 점검 후
-- COMMIT;
-- ROLLBACK; -- 일부 DDL은 롤백 가능/불가가 달라서 사전 테스트 필수

실무 팁

• 마이그레이션 도구(예: SSDT/DACPAC, EF Migrations)의 Down 스크립트를 반드시 관리.
• 프로시저/뷰 수정은 이전 버전 스크립트를 저장소에 보관.

---

7) 사후 복구 시나리오 (이미 커밋됐다면)

7-1) 포인트 인 타임 복구(전체 DB 되돌리기)

• 전제: 전체 백업 + 로그 백업 체계 운영.

 

-- 복구 예시(요지: 원하는 시각으로 STOPAT)
RESTORE DATABASE MyDB FROM DISK='full.bak' WITH NORECOVERY;
RESTORE LOG MyDB FROM DISK='log_2025_0922.trn' 
    WITH STOPAT = '2025-09-22T10:45:00', RECOVERY;

주의: DB 전체 시점 이동이라 운영에 영향 큼 → 보통 별도 복구용 DB로 리스토어 후, 차분 데이터만 추출/머지.

7-2) 시스템 버전형 테이블(Temporal Table) 활용

• 전제: 테이블에 시스템 시간 버전이 켜져 있어야 함.

SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2025-09-22 10:40:00'
WHERE Id = 1001;

 

→ 과거 스냅샷에서 값 조회 후 정상 테이블에 반영.

7-3) CDC(Change Data Capture)/감사 테이블

• 변경 이력에서 전/후 이미지를 조회해 반대로 적용.

---

8) 실무 체크리스트

• 변경 전 BEGIN TRAN으로 가두고 확인 후 COMMIT
• 대량 작업은 OUTPUT으로 백업(되돌릴 증빙 확보)
• 야간 배치 + 작은 단위(TOP N)로 나눠 실행
• 인덱스/트리거/외래키 영향 확인(순서/무결성)
• 정기 백업/로그 백업 유지 + 복구 리허설
• 스키마 변경은 Down 스크립트와 함께 배포

---

✅ 한 장 요약 (추천 루틴)

1. 변경 전: BEGIN TRAN → 대상 SELECT로 수량/범위 확인
2. 백업: SELECT ... INTO _bak_YYYYMMDD 또는 OUTPUT 캡처
3. 변경: UPDATE/DELETE/INSERT 실행
4. 검증: 영향 건수·무결성 체크
5. 확정: COMMIT (문제 시 ROLLBACK + 백업 데이터로 되돌리기)
6. 사후: 로그/문서화, 배치/모니터링 개선