[MSSQL] SQL Server 에이전트 이용하여 DB 자동 백업 설정하기

데이터베이스를 사용하다 보면 예기치 못한 문제로 데이터베이스가 손상되거나 서버가 망가져 재부팅이 되지 않는 경우가 있습니다. (물론 그런 일이 발생하지 않도록 서버를 관리해야 할테고, 아주 예외적일 수 있지만, 어떤 예기치 못한 상황에서 데이터를 안전하게 보존하기 위해서는 정기적인 백업 시스템이 필요합니다.

 

정말 안전한 데이터 베이스 관리를 위해서는 분산 DB 관리 시스템을 구축하거나 데이터베이스 미러링, 기타 원격 백업 등을 해야합니다.

여기서는 간단히 SQL Server 에이전트를 통해서 DB를 매일 정기적으로 백업하는 기능을 구현해보겠습니다.

 

사실 SQL Server 에이전트는 DB백업만을 위해서 제공하는 기능이 아니고, DB에서 정기적으로 무언가를 수행할 필요가 있는 경우, 이를 구현할 수 있도록 지원하는 기능입니다. 사실 MSSQL에서 DB백업을 위에 유지 관리 계획 이라는 기능을 별도로 또 제공하고 있는데, 이는 다음 기회가 생기면 포스팅 하도록 하겠습니다.

 

본 게시물은 MS SQL Server 2008 R2 를 기준으로 작성되었습니다.

 

서두가 너무 길면 지루하니 각설하고, 바로 SQL Server 에이전트를 통해 DB를 자동백업하도록 해봅시다! (스크린 샷 이미지가 기존에 만든 것을 가지고 하니, 날짜가 조금 예전 것이거나 할 수 있습니다.)

---------------------------------------------------------------------------------------------

1. Microsoft SQL Server Management Studio 를 실행시킵니다. 좌측 개체 탐색기 트리 메뉴에서 하단으로 조금 내려가 보면 SQL Server 에이전트가 보일 것입니다.

 

트리를 확장시켜 작업 메뉴에서 오른쪽 마우스 버튼 클릭하면 뜨는 컨텍스트 메뉴에서 [새 작업] 메뉴를 선택합니다.

 

 

 

 

2. 새 작업을 위한 다이얼로그가 뜹니다. 다이얼로그가 뜨면 기본으로 좌측 메뉴에서 일반 메뉴가 선택되어 있습니다. 여기서는 우리가 만들 작업에 대한 이름이나 설명 등을 입력하는 곳입니다.

 

테스트로 자동 백업을 위한 작업을 만드려고 하니, Test AutoBackup 이라 이름 붙여보았습니다. 기타 소유자나 설명 등은 그대로 유지하였습니다. 필요하면 설명 같은 것을 작성해도 좋습니다.

 

 

 

3. 이제 좌측에서 단계 메뉴를 선택합니다. 실질적인 작업을 구현하는 곳입니다. 하나의 작업만 만들 수도 있고 여러개의 작업을 만들어 순차적으로 배치할 수도 있습니다.

 

현재 화면을 보니 아직 등록된 작업이 하나도 없습니다. 아래에 있는 [새로 만들기] 버튼을 클릭하여 작업을 만들어 보겠습니다.

 

 

 

 

4. 작업 단계 속성이라는 새로운 다이얼로그가 뜹니다. 새로 만들 작업이 실제 어떤 작업을 하는지 쿼리를 작성하는 곳입니다.

 

이번 작업의 단계 이름은 백업을 하는 것이니 '백업 단계'라고 하였으며, 작업 유형은 T-SQL(Transaction-SQL) 쿼리로 직접 작성할 것이니, 이를 선택합니다.

 

트랜잭션SQL :  http://ko.wikipedia.org/wiki/%ED%8A%B8%EB%9E%9C%EC%9E%AD%ED%8A%B8_SQL

 

스크린샷 이미지를 보면 이미 백업을 위한 코드가 작성되어 있습니다.

 

 

 

 

 

(1)

DECLARE @backupDate DATETIME
DECLARE @strTodayLong CHAR(20)
DECLARE @strToday CHAR(8)
DECLARE @fileName NVARCHAR(100)

 

(2)

SET @backupDate = GETDATE();

 

(3)

SET @strTodayLong = ''
SET @strTodayLong = CONVERT(CHAR(20), @backupDate, 112)
SET @strToday = LEFT(@strTodayLong, 8)

SET @fileName = 'D:\Backup\Test\Test_Auto_Backup_'

+ @strToday
+ '.bak'

 

(4)

WAITFOR DELAY '00:00:02'

 

(5)

BACKUP DATABASE Test_DB To disk = @filename

 

* 위의 괄호()의 숫자는 실제 코드가 아닙니다. 나누어 설명하기 위해 번호를 붙였습니다.

 

SQL 프로시져 작성할 때, 쿼리 문법과 동일합니다.

(1) 사용할 변수를 선언(Declare)합니다.

 * 오늘날짜(백업날짜)를 위한 변수, 오늘날짜를 기반으로한 문자열을 생성하기 위한 2개의 변수, 최종 백업 파일명을 위한 변수를 선언합니다.

 

(2) 오늘 날짜를 얻어와 @backupData 변수에 설정합니다.

 

(3) 오늘 날짜를 년월일시분초 형태의 문자열로 변환하여 @strTodayLong 변수에 저장합니다.

 백업은 하루에 한번만 실행되도록 스케쥴을 짤 예정이므로 @strTodayLong에서 년월일에 해당하는 부분만 가져와 @strToday 변수에 저장합니다.

 그리고 최종적으로 D드라이브의 Backup\Test 디렉토리에 있는 Test_Auto_Backup_(YYYYMMDD).bak 형태의 파일 명을 만들어 @fileName에 저장합니다.

 

(4) 잠시 2초동안 대기합니다. 보통 이전 업무 처리에 어느정도 시간이 소요되는 경우 문제 발생을 최소화하기 위해 딜레이 값을 줍니다. (그런데 현재 코드에서는 별로 딜레이 될 요소가 없는데, 그냥 넣어 보았습니다. ㅡ.ㅡ; 문자열 세팅하는데, 2초 이상 걸리는 서버 시스템은 현재 지구상에 없을 겁니다..;;)

 

(5) DB를 파일로 백업합니다. 

 * BACKUP 명령어를 사용하여 Test_DB 명을 가진 데이터베이스를 디스크로 백업합니다. 여기서 디스크는 물리적인 파일을 말하며, 즉 파일명이 @filename 인 파일로 DB를 백업하는 작업을 수행합니다.

 

 

5. 작업 단계 속성 다이얼로그의 일반 메뉴에서 설정 작업이 완료되었으면 좌측에서 고급 메뉴를 선택합니다. 여기서는 백업 작업이 성공적으로 수행된 경우, 실패한 경우의 처리 및 재시도 횟수 및 간격 등에 대한 것을 설정합니다.

 

기본으로는 성공한 경우 다음 단계로 이동 (다음 단계가 없다면 보통 성공 보고와 함께 작업을 종료함), 실패한 경우 실패 보고와 함께 작업을 종료합니다.

 

설정이 완료되었다면 하단의 확인 버튼을 눌러 작업 단계 속성 설정을 종료합니다.

 

 

 

6. 방금 만든 작업이 이전 다이얼로그에 나타납니다. 백업 단계 업무가 추가되었습니다. 단계 수행 코드나 고급 설정 정보를 수정하시려면 해당 작업을 선택 후, 하단의 편집 버튼을 클릭하면 됩니다.

 

현재 예제에서는 하나의 작업만 만들었지만, 다양한 작업을 더 작성하여 추가할 수 있습니다. 여러개의 작업이 존재하는 경우, 위에서 부터 순차적으로 실행됩니다.

 

 

 

7. 이제 백업을 수행하는 작업을 만들었는데, 이를 언제 실행되도록 해야 할까요? 이 작업을 일정 메뉴를 통해서 설정할 수 있습니다.

 

좌측의 일정 메뉴를 선택하여 일정 화면으로 이동한 다음 하단의 새로 만들기 버튼을 클릭하면 새 작업 일정 작성을 위한 다이얼로그가 나타납니다.

 

여기에서는 바로 이전 단계에서 만든 작업(들)을 언제 수행할지 결정하는 부분입니다.

 

작업 이름은 임의로 작성 하시면 되고, 세부적으로 일정에 대해 다음과 같은 설정을 하실 수 있습니다.

일정 유형 : 언제 작업을 실행할 것인가? (SQL Server 에이전트가 시작될 때, 반복, 단 한번만 등)

 

* 한 번 발생 : 유형에서 [한 번]을 선택한 경우 활성화되면 작업이 수행될 특정 일자와 시간을 설정합니다.

* 빈도 : 유형에서 [되풀이]를 선택한 경우 활성화되며 일별, 주별(특정 요일 등), 월별(특정 일자 등)을 선택할 수 있으며, 하루에 몇 시간 간격으로 반복시킬 것인지, 특정 시간에만 수행시킬 것인지 등을 설정합니다.

 

 

설정이 완료되었으면 확인 버튼을 클릭합니다.

 

8. 새 일정이 등록되면 아래와 같이 일정 목록에 일정이 나타납니다. 일정 또한 단계와 같이 여러개의 일정을 만들 수 있습니다.

매일 오전 3시 55분에 단계(작업)가 수행되도록 설정하였습니다.

 

이제 Test_DB의 백업이 별도로 관리자가 백업을 수행하지 않도록 매일 3시 55분에 최신DB를 D드라이브에 Test_Auto_Backup_YYYYMMDD.bak 라는 파일명으로 백업 파일이 생성될 것입니다.

 

 

 

 

좌측 메뉴의 경고, 알림, 대상 등은 작업에 대해 부가적인 것들을 더 설정할 수 있습니다.

작업 수행 시, 발생되는 오류에 따른 경고에 대해 정의할 수 있으며, 문제가 발생한 경우(또는 정상적으로 수행된 경우)에 관리자에게 메일이나 호출 등을 설정할 수 있도록 지원합니다.

 

 

9. 확인을 눌러 작업 등록을 모두 완료하면 SQL Server 에이전트에 새 작업 스케쥴이 등록된 것을 보실 수 있습니다.

 

Test AutoBackup 03:55 D 라는 작업이 보입니다.

 

 

그런데 아직 해당 작업이 정상적으로 설정되었는지, 코드에 문제는 없는지는 확인이 안된 상태입니다. 프로시져 처럼 바로 SQL코드에 문제가 있는지 오류를 검증할 수도 없습니다.

 

사실 '단계' 단계에서 T-SQL 작성 화면의 코드 작성 명령 메뉴(작성란 좌측)를 보면 '구문 분석'이라는 버튼이 있습니다. T-SQL 작성한 후에 구문 분석을 통해서 구문이 논리적으로 문제가 없는지, 잘못된 명령어를 사용하지는 않았는지는 검증해 주지만 실제 백업하려는 DB가 존재하는지 등에 대한 것은 검증해주지 않습니다.

즉 코드 문법은 모두 올바르게 작성했지만, 정작 백업하려는 DB이름에 오타가 나서 존재하지 않는 다면 어떻게 될까요?

 

10. 위와 같은 문제가 있는지 검증하기 위해서 방금 작성한 작업을 바로 실행해 볼 수 있습니다. 바로 SQL Server 에이전트에 등록된 작업에서 마우스 우측 버튼을 클릭한 후, 컨텍스트 메뉴에서 [작업 시작 단계] 메뉴를 선택합니다.

 

 

 

 

11. 작업 시작 단계를 선택하면 해당 작업을 즉시 수행합니다. 수행 후, 정상적으로 작업이 완료되면 모두 성공으로 표시됩니다.

 

작업이 성공적으로 수행되지 않으면 실패라고 뜨며, 그 실패 원인은 메시지에 나타납니다. 실패의 경우 더 상세한 실패 원인은 이벤트 로그(SQL Server 로그)를 통해서 볼 수 있습니다.

 

 

 

 

SQL Server 에이전트는 원하는 특정 시점, 기간 (또는 반복)에 DB에서 어떤 작업이 수행되기를 원할 때, 작업과 일정을 짤 수 있는 간단하고도 강력한 기능을 제공합니다.

스케쥴링이 필요한 DB작업이 필요하다면 SQL Server 에이전트를 활용하세요!

 

- 끝 -