MSSQL

MSSQL 로그/데이터 백업/복구 SHIRNK

minies 2024. 5. 27. 20:50
반응형

## MS 백업 유형

  • 전체백업 (FULL) : 모든 데이터 파일, 트랜잭션 로그의 활성 부분 일부를 백업 
  • 차등 백업 : 마지막 FULL 백업 이후 변경된 데이터 백업
  • 트랜잭션 로그 백업 : DB 변경 사항이 기록된 트랜잭션 로그 파일 백업
  • 파일 및 파일 그룹 백업 : 파일. 파일 그룹에 있는 모든 데이터를 백업
  • 부분 백업 : PRIMARY 파일 그룹과 읽기/쓰기가 모두 가능한 파일 그룹만을 백업하며 읽기 전용 파일 그룹은 백업을 제외할 수 있어 백업 , 복원 속도 향상
  • 차등백업 : 차등백업과 부분 백업의 혼합 모델
  • 복사 전용 백업 : 백업 시퀀스에 영향을 주지 않는 복사 전용 백업으로 차등 기반으로 사용할 수 있는 전체 백업을 만들거나 트랜잭션 로그를 자르지 않는 복사전용 로그 백업 생성
  • 미러된 백업 : 한 번의 명령으로 2개 이상의 백업 장치에 동일한 데이터를 백업하여 하나 이상의 백업파일 생성 가능

 

## 로그 백업

-- FULL 백업 받은 후 다량의 DML발생으로 로그가 대량으로 쌓인다면 로그파일이 꾸준히 증가할 수 있음

-- 이럴 경우를 대비하여 수동으로 LOG 백업 해주는 과정도 필요

-- 물리적 로그파일(LDF)이 감소하는건 아니지만 더 이상 증가하지는 않음(비워줬기 때문에)

BACKUP LOG [SQLTAG] TO DISK = 'D:/SQL/BACKUP/SQLTAG_LOG.TRN'

 

-- DB 로그 공간 사용량 통계 확인
dbcc SQLPERF(LOGSPACE)

 

 

## 만약 로그파일이 커져서 SHRINK 해주어야 할 때

-- 파일 이름 확인
SP_HELPFILE

-- DB 축소 작업 사이즈 잘 확인해야함
DBCC SHRINKFILE(AdventureWorks2017_log, 10)
GO

-- 사이즈 확인
DBCC SQLPERF(LOGSPACE)

 

※ 내부적으로 어떤 과정을 거치는지는.. 잘 모르겠음..

 

 

## 트랜잭션 로그 복구 필요시

USE MASTER
GO

-- 풀백업 복원
-- (여기서 REPLACE 옵션은 동일한 이름의 DB가 존재하면 기존 DB를 덮어쓴다는 의미)
RESTORE DATABASE [SQLTAG] FROM DISK ='D:\SQL\BACKUP\SQLTAG_FULL.BAK' WITH NORECOVERY, REPLACE
GO


-- 트랜잭션 로그 복원
RESTORE LOG [SQLTAG] FROM DISK = 'D:\SQL\BACKUP\SQLTAG_LOG.TRN' WITH RECOVERY
GO

 

 

 

 

 

## 데이터 백업

  • 전체 백업 차등 백업
IF EXISTS (
SELECT * FROM SYS.DATABASES WHERE NAME =N'SQLTAG'
)
DROP DATABASE SQLTAG
GO

-- 전체 DB 복원 (NORECOVERY)
RESTORE DATABASE [SQLTAG] FROM DISK = 'D:\SQL\BACKUP\SQLTAG.BAK'
WITH NORECOVERY
GO


-- 차등백업 데이터를 사용하여 복원(RECOVERY)
RESTORE DATABASE [SQLTAG] FROM DISK ='D:\SQL\BACKUP\SQLTAG_DIFF2.BAK' WITH RECOVERY
GO

 

※ 복원해야할 파일이 여러개일 경우 (전체백업+차등백업) 마지막 백업 파일을 제외하고는 NORECOVERY 옵션명시필요

→ 모든 복원 작업이 완료되지 않았기때문에 아직 커밋되지 않은 트랜잭션에 대해 롤백을 수행하지 않고 복원 진행상태 유지

→ 여기서 RECOVOERY해버리면 그뒤로는 백업파일이 있어도 복구 불가

→ RECOVERY 상태가 됐다는건 이제 DB가 사용할 수 있는 온라인 상태가 되었다는 의미

 

 

 

  • 파일그룹별 백업

-- 전체 DB백업/복구에는 너무 많은 시간이 걸리기 때문에 파일 그룹별로 나누어서 백업/복구 해야함

BACKUP DATABASE [SQLTAG] FILGROUP = 'PRIMARY' TO DISK = 'D:\SQL\BACKUP\SQLTAG_PRIMARY.BAK'

 

 

  • 차등 부분 백업

-- 부분백업으로 읽기 쓰기가 모두 가능한 파일 그룹만 백업하는 경우도 있지만 많지 않음

BACKUP DATABASE [SQLTAG} READ_WRITE_FILEGROUP TO DISK = 'D:\SQL\BACKUP\SQLTAG_RW_FG.BAK'

 

 

  • 복사 전용 백업

-- 현대 DB 를 테스트 목적으로 다른곳으로 옮기고자 할 때 유용 

BACKUP DATABASE [SQLTAG] TO DISK = 'D:\SQL\BACKP\SQLTAG_COPY_ONLY.BAK' WITH COPY_ONLY;

 

 

 

  • 미러된 백업

-- 서로 다른 백업 장치에 동시에 백업하여 하나가 깨져도 복구 가능

BACKUP DATABASE [SQLTAG] TO DISK ='D:\SQL\BACKUP\SQLTAG.BAK' MIRROR TO DISK = 'E:\SQL\BACKUP\SQLTAG.BAK' WITH FORMAT

 

 

 

 

 

 

 

 

## 트랜잭션 로그 관리하는 방법 유형

※ 모델 DB에 설정된 복구 모델을 따르게 된다

  • 단순 : 트랜잭션 로그를 백업할 수 없으며, 체크포인트가 발생할 때마다 트랜잭션 로그가 자동으로 비워짐
  • 대량로그 : 대량 변경 작업을 처리한느 BULK 명령을 수행할때는 최소한의 로그 정보만 기록하며 이외 트랜잭션은 전체와 동일하게 기록
  • 전체 : 모든 트랜잭션 기록
SELECT NAME, (SELECT DATABASEPROPERTYEX(NAME, 'RECOVERY'))
RECOVERYMODEL FROM MASTER..SYSDATABASES ORDER BY NAME;


ALTER DATABASE [SQLTA] SET RECOVERY SIMPLE;

 

 

※ MSSQL 에서의 체크포인트란 : SP_CONFIGURE옵션에 있는 RECOVERY INTERVAL 값을 주기로 하여 체크포인트가 발생하며 직접 CHECKPOINT 명령을 수행했을때 메모리에 있는 DIRTY DATABASE PAGE가 디스크로 내려가게 된다.

 

 

 

반응형
저작자표시 (새창열림)