[MsSQL] 데이터베이스 모델과 백업, 스냅샷 방법 및 종류 알아보기

MSSQL 은 데이터베이스에 백업을 위한 3가지 모델(전체, 대량 로그, 단순)을 지원하며,

백업의 종류는 전체, 차등, 로그 백업으로 구성됩니다.

이해가 좀 어려울 수 있는 데, 간단히 설명드리겠습니다.

모델은, 트랜잭션 로그파일에 어떻게 기록하는지에 대한 정의를 내리는 부분입니다.

백업은, 말그대로 현재 데이터 파일을 물리적으로 복사한다 라고 생각하면 됩니다.

데이터베이스의 복구 모델

데이터베이스 복구 모델은 전체(Full), 대량 로그(Bulked Log), 단순(Simple) 세 가지 모델이 있습니다.

예를 들어 SQL Server 에서 제공하는 AdventureWorks2017 데이터베이스의 복구 모델은 단순으로 설정되어 있습니다.

1. 전체 복구 모델

데이터베이스가 문제가 발생할 때 문제가 발생한 시점까지 복구가 가능한 모델입니다.

이것이 가능한 이유는 데이터가 변경되는 모든 작업과 내용이 트랜잭션 로그 파일에 기록되기 때문입니다.

또한, 전체 복구 모델은 원하는 시간 위치로 복구가 가능합니다.

전체 복구 모델은 전체 백업, 차등 백업, 로그 백업이 가능합니다.

 

2. 대량 로그 복구 모델

대량 로그 작업이 발생하면, 로그 파일에 이러한 작업이 일어났다는 것만 기록할 뿐, 추가되거나 변경된 내용은 트랜잭션 로그 파일에 기록하지 않습니다.

전체 복구 모델과 마찬가지로 문제가 발생한 시점까지는 복구가 가능하지만, 원하는 시간 위치로 복구할 수는 없습니다.

대량 로그 작업이 있을 때 발생하는 많은 내용을 트랜잭션 로그 파일에 기록하지 않음으로써 속도를 향상시킬 수 있습니다.

대량 로그 복구 모델은 전체 백업, 차등 백업, 로그 백업이 가능합니다.

 

3. 단순 모델

트랜잭션 로그 파일에 아무것도 기록되지 않는다고 생각하면 됩니다.

사실 기록되는 것은 있으나, SQL Server 가 로그 파일을 관리하여 알아서 비우기 때문에 사용자는 로그 파일의 내용을 간섭할 수 없기 때문입니다.

트랜잭션 로그 파일을 사용자가 관리할 수 없기 때문에 전체 백업과 차등 백업만이 가능합니다.

단순 모델은 문제점이 발생된 시점까지 데이터를 되돌릴 수가 없고, 마지막에 백업받은 데이터까지만 복구가 가능합니다.

데이터베이스의 모델을 변경하는 구문은 다음과 같습니다.

ALTER DATABASE AdventureWorks SET RECOVERY FULL;               -- 전체 복구 모델

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;  -- 대량 로그 복구 모델

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;            -- 단순 모델 

대부분의 백업 기준은 전체 복구 모델을 기준으로 합니다.

1. 전체 백업

데이터베이스 전체를 백업한다는 뜻입니다. 즉, 데이터베이스 개체, 시스템 테이블, 데이터를 모두 백업합니다.

또한 백업이 진행되는 동안 발생되는 트랜잭션 로그 중 필요한 부분도 백업합니다

전체 백업을 꼭 받아야 하는 경우는 다음과 같습니다.

 - 처음 데이터베이스를 생성했을 때

 - 트랜잭션 로그를 강제로 비웠을 때

 - 데이터베이스에 변경이 생겼을 때

 - 차등 백업이나, 로그 백업을 하기 전

전체 백업 구문은 다음과 같습니다.

 BACKUP DATABAASE AdventureWorks TO disk = 'C:\백업폴더\advbackup.bak';

만약 백업폴더 라는 폴더가 없으면 오류가 발생하므로 미리 폴더를 생성해야 합니다.

전체 백업을 받기전에 백업될 데이터베이스의 크기를 알고있어야 백업에 필요한 공간을 산정할 수 있으므로 sp_spaceused 와 같은 프로시저로 먼저 측정합니다.

2. 차등 백업

마지막 전체 백업 이후에 변경된 모든 데이터를 백업하는 것입니다.

그러므로 차등 백업은 혼자서 존재할 수 없으며, 반드시 전체 백업을 받은 이후에 수행해야 합니다.

차등 백업의 구문은 다음과 같습니다.

BACKUP DATABASE AdventureWorks TO disk = 'C:\백업폴더\advbackup_dif.bak' WITH DIFFERENTIAL;

  

3. 트랜잭션 로그 백업

실제 데이터 파일(mdf 혹은 ndf)의 내용을 백업하는 것이 아니라 로그 파일(ldf)에 기록된 로그를 백업합니다.

트랜잭션 로그 백업을 하려면 전체 백업을 1회 이상 수행해야 합니다.

트랜잭션 로그 백업을 하게 되면, 로그 파일을 비우기 때문에 로그 파일에는 아무런 내용도 기록되지 않습니다.

만약 중간의 로그 백업의 내용이 유실된다면 유실된 이후의 모든 로그 백업된 자료는 복원할 수가 없습니다.

로그 백업은 다음과 같은 특징이 있습니다.

 - 로그 백업은 변경된 내용만을 백업하기 때문에 대개는 백업속도가 무척 빠릅니다. 일반적으로 실무에서 사용되는 대용량의 데이터에서 실제 변경되는 내용은 많지 않기 때문입니다.

 - 복원되는 속도는 느립니다. 데이터를 백업받은 경우는 데이터를 바로 DB에 복사하는 형식이지만, 로그를 백업받은 경우에는 DML문을 다시 수행해야 하기 때문입니다.

 - 대량의 데이터 변경이 일어난 경우에는 로그 백업이 바람직하지 않습니다. 복원할 양이 많기 때문에 속도가 느려지기 때문입니다.

따라서, 대량의 데이터 변경이 일어난 후에는 전체 백업을 해주고, 다시 처음부터 로그 백업을 해 주는 것이 바람직합니다.

로그 백업의 구문은 다음과 같습니다.

BACKUP LOG AdventureWorks TO disk = 'C:\백업폴더\advbackup_log.bak';

전체 백업과 차등 백업에서는 로그에 대해서 신경쓰지 않습니다. 즉, 트랜잭션 로그 파일을 비우지 않습니다.

그렇다면 로그 백업을 하지 않고 전체 백업과 차등 백업만 하게 되면 어떻게 될까요?

한참 흐에 트랜잭션 로그가 꽉 차서 더이상 데이터의 변경이 불가능해질 뿐 아니라, DBMS 시스템에도 문제가 생길 수 있습니다.

그러므로, 로그 파일이 꽉 차기 전에 주기적으로 로그 파일을 비워주어야 합니다.

로그 백업을 하지 않고 로그 파일을 강제로 비우려면 데이터베이스 복구 모델을 '단순' 복구 모델로 전환해 주면 됩니다.

백업 시 사용되는 기능과 옵션

1. 백업 압축

백업 압축은 Enterprise 혹은 Developer 버전 이상에서 지원하는 기능입니다.

SQL Server를 처음 설치하면 이 백업 압축이 기본으로 설정되어 있지 않으므로, BACKUP 명령에서 WITH COMPRESSION 옵션을 적어 주어야 합니다.

만약 백업할 때 기본적으로 압축이 되도록 설정하려면, 인스턴스 속성의 데이터베이스 설정에서 백업 압축을 선택하면 됩니다.

백업 압축은 백업 결과의 크기가 줄어들기 때문에 저장 공간을 아낄 뿐만 아니라, I/O 도 줄어들기 때문에 속도가 향상되는 효과가 있습니다.

백업 압축시에는 자동으로 내부적으로 체크섬이 수행됩니다.

 BACKUP DATABASE AdventureWorks To disk = 'C:\백업폴더\advbackup.bak' WITH COMPRESSION;

2. 미러 백업

하나의 백업이 아니라 다른 장치에도 동일한 백업을 수행함으로 써 하나의 백업이 깨지더라도 문제가 없도록 하는 기능입니다.

구문은 다음과 같습니다.

 BACKUP DATABASE AdventureWorks To disk = 'C:\백업폴더\advbackup.bak'

                                       MIRROR To Disk = 'D:\백업폴더\advbackup.bak' WITH FORMAT;

3. 분할 파일 백업

백업될 용량이 대용량이라면 백업 시간이 상당히 오래 걸릴 수 있습니다. 이런 경우 여러 개의 파일에 나눠 동시에 백업된다면 백업되는 시간을 줄일 수 있습니다.

주의 할 점은 백업받은 것 중 하나라도 문제가 있다면 백업한 모든 것을 사용할 수 없다는 점입니다.

구문은 다음과 같습니다.

BACKUP DATABASE AdventureWorks To disk = 'C:\백업폴더\advbackup_1.bak'

                                                      ,disk = 'D:\백업폴더\advbackup_2.bak'

                                                      ,disk = 'E:\백업폴더\advbackup_3.bak';

   

4. 체크섬 기능

백업할 때 백업받은 데이터에 이상이 없는지 확인하면서 백업하는 기능입니다.

백업 중에 혹시 있을 수 있는 데이터의 오류를 한 번 검증함으로써 데이터의 신뢰성을 향상시킨 기능으로 WITH CHECKSUM 이라고 붙여주면 됩니다.

구문은 다음과 같습니다.

BACKUP DATABASE AdventureWorks To disk = 'C:\백업폴더\advbackup.bak' WITH CHECKSUM;

5. 백업 장치 초기화

WITH INIT 을 붙이면 백업 장치가 초기화 됩니다.

즉 기존에 백업된 내용이 모두 삭제되고 새로 백업 장치의 맨 앞부터 백업에 사용됩니다. 기본값은 WITH NOINIT 입니다.

BACKUP DATABASE AdventureWorks To disk = 'C:\백업폴더\advbackup.bak' WITH INIT;

  

6. 다중 백업 장치 초기화

WITH FORMAT 을 붙이면 다중 백업 장치(여러 개의 디스크에 동시에 백업하는 것)를 초기화한 후 백업을 수행합니다.

이는 대개 기존에 어떤 다른 내용이 들어있는 여러 개의 디스크에 처음 백업을 수행할 때 붙여줍니다. 기본값은 WITH NOFORMAT 입니다.

BACKUP DATABASE AdventureWorks To disk = 'C:\백업폴더\advbackup.bak' WITH FORMAT;

  

7. 비상 로그 백업

WITH NO_TRUNCATE 옵션을 사용하면 데이터베이스에 이상이 발생해도 로그 백업을 진행할 수 있습니다.

중요한 옵션이기 때문에 잘 기억해 두어야 합니다. :)

BACKUP LOG AdventureWorks To disk = 'C:\백업폴더\advbackup_log.bak' WITH NO_TRUNCATE;

  

백업 장치

백업 장치란 백업을 받는 물리적인 장치의 논리적인 이름입니다.

만약 파일에 백업을 받은 경우에 매번 파일의 경로와 파일명을 기억해야만 하게되며, 이러한 파일의 경로와 파일명을 아예 백업 장치로 생성해 놓으면 백업 장치 이름만 기억하면 되기 때문에 편리합니다.

https://mozi.tistory.com/298

스냅숏이라고 하는 개념이 있습니다.

백업이라고 보기는 힘들지만, 데이터베이스를 쿼리를 수행하는 시점에서 복사하기 때문에 백업의 개념에 포함시켰습니다.

CREATE DATABASE AdventureWorks_D20190320 ON (NAME = AdventureWorks2017,                -- 원본 데이터베이스 행 데이터의 논리적 이름 (데이터베이스 속성 - 파일에서 확인 가능)

                                             FILENAME = 'C:\Backup\Adv_D20190320.mdf') -- 새로 생성할 데이터 파일 이름

AS SNAPSHOT OF AdventureWorks;            -- 원본 데이터베이스 이름

복원에 관련한 포스팅은 아래 url을 참고해 주세요.

https://mozi.tistory.com/297