두 인스턴스 간 Native Backup/Restore 유지 관리 계획

개발 테스트 환경을 위해 운영 데이터베이스의 FULL BACKUP 데이터를 다른 인스턴스에서 RESTORE 하는 스크립트를 아래와 같이 정리하였습니다. 마이크로소프트 기술지원으로 제공된 SQL Script 를 참조하며 환경에 맞게 수정하였으며 해당 스크립트를 블로그에 공개하지 않습니다.

[환경]

SQL Server 2005 SP1 Later 

Default Instance : MSSQLSERVER (Source)

Named Instance : SEC (Destination) 

[시나리오]

1. Source Instance 에 있는 LAIGO 데이터베이스를 D:\SQL\BACKUP 폴더에 LAIGO_DB_FULL_YYYYMMDD.BAK 파일 형식으로 매일 1회 Full Backup 합니다.

2. 위에서 백업된 데이터를 Destination Instance 에서 LAIGO 라는 데이터베이스 이름으로 D:\SQL\DATA 폴더에 매일 1회 Restore 합니다. 단, Destination Instance 에서 LAIGO라는 데이터베이스 이름이 이미 존재할 경우 LAIGO_YYYYMMDD로 RENAME 합니다.

3. Full Backup 데이터는 2일을 보관하며, 복원한 데이터베이스도 2개(2일)만 유지하며 이외는 삭제합니다.

[사전준비]

1. SQL Server 2005 2개의 인스턴스  

2. 사용자 프로시저 

  가. Source Instance 

    1) up_DBA_BACKUP_DB.sql 

        - 데이터베이스 이름과 백업 종류를 결정하여 지정된 폴더에 백업합니다.

  나. Destination Instance 

    1) up_DBA_RENAME_DB.sql

       - 변경할 데이터베이스 이름을 인자로 받아 RENAME 합니다.

    2) up_DBA_RESTORE_DB.sql

       - 복원할 데이터베이스 이름과 데이터 파일이 복원될 경로를 인자로 받아 복원합니다. 

    3) up_DBA_DROP_DB_EXPIRED.sql

       - 삭제할 데이터베이스 이름과 만료 일자를 인자로 받아 데이터베이스를 삭제합니다. 

[백업 데이터베이스 설정 / Source Instance]

1. MSSQLSERVER(Source), SEC(Destination) 인스턴스를 연결합니다. 유지 관리 계획을 사용해야 하므로 SQL Server Agent 서비스도 시작합니다. (아래 그림과 같이 SEC 인스턴스의 SQL Server Engine 버전이 낮습니다. 테스트 환경이므로 그냥 무시하고 진행하겠습니다)

2. 준비된 up_DBA_BACKUP_DB.sql 스크립트를 Source 인스턴스 Master Database 에 사용자 프로시저로 등록합니다.

   스크립트를 통해 데이터베이스명, 백업 파일을 저장할 경로, 백업 Type 을 지정할 수 있습니다.

3. SSMS - 관리 - 유지 관리 계획 - 새 유지 관리 계획

  가. T-SQL 문 실행 작업 등록 

      EXEC up_DBA_BACKUP_DB @user_db_nm = 'LAIGO', @folder_path = 'D:\SQL\BACKUP', @backup_type = 'DB'

      -- LAIGO 데이터베이스의 Full Backup 데이터를 D:\SQL\BACKUP 폴더에 저장합니다.

  나. 유지 관리 정리 작업 등록

       1) 백업 파일 

       2) 삭제 대상 지정 : D:\SQL\BACKUP 의 BAK 파일

       3) 보존 기간 : 2일 (오늘과 하루 전,  총 2일 데이터만 남겨 놓습니다)

  다. 작업 일정 속성 등록

      1) 매일 지정된 시간에 작업이 수행될 수 있도록 일정을 지정한 뒤 지금까지 생성한 작업을 저장합니다.

4. 작업 실행

  가. 시스템 날짜를 변경하며 작업을 실행해 보면 아래와 같은 형식의 백업 파일이 최근 2일 자료만 남게 됩니다.

       LAIGO_DB_FULL_20090622.BAK, LAIGO-DB_FULL_20090623.BAK 

[복원 데이터베이스 설정 / Destination Instance]

1. Destination Instance 의 Master 데이터베이스에 준비해 놓은 3개의 프로시저를 생성합니다. 환경에 맞게 스크립트를 수정해서 사용합니다.

   (up_DBA_RENAME_DB.sql, up_DBA_RESTORE_DB.sql, up_DBA_DROP_EXPIRED.sql)

2. SQL Server Agent  예약 작업 등록

  가. 1단계 : EXEC master..up_DBA_RENAME_DB 'LAIGO'

                 이 단계가 수행되면 LAIGO 데이터베이스 이름이 LAIGO_YYYYMMDD 로 변경됩니다. 

  나. 2단계 : up_RESTORE_DB 'LAIGO', 'D:\SQL\DATA'

                 복원되어 생성될 데이터베이스 이름과 MDF, LDF가 위치할 경로를 지정합니다.

  다. 3단계 : EXEC master..up_DBA_DROP'LAIGO', 1

                 삭제할 데이터베이스명과 보존 기간 지정  (3은 오늘과 전일 데이터를 보존함)

  라. 시작 단계 지정 및 일정 등록 

[실행 결과]

1. D:\SQL\Backup 폴더에 백업 파일도 2개, D:\SQL\Data 폴더에 데이터베이스 파일도 2 SET 로 유지됩니다. 

2. 생성된 작업과 데이터베이스 이름은 아래와 같습니다. 

[참고자료]

[Private] 두 인스턴스 간 Native Backup/Restore 유지 관리 계획 - SCRIPT

http://laigo.kr/258

작성자 : Lai Go / 작성일자 : 2009.06.22