데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SQL 서버 감사가 있는 데이터베이스 복원 후 설정

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

데이터베이스에 서버 감사(Server Audit)가 설정되어 있는 경우 해당 데이터베이스를 복원 하면 감사 메커니즘은 감사 데이터를 캡처 할 수 없는 문제가 발생한다. 다음 방법을 통하여 감사 데이터를 캡처 할 수 있도록 설정 하자.

 

기존 데이터베이스에 감사를 만든다.

USE master

GO

 

CREATE SERVER AUDIT [Test_Audit]

TO FILE

( FILEPATH = N'C:\SQL_Data\'

,MAXSIZE = 100 MB

,MAX_ROLLOVER_FILES = 90

,RESERVE_DISK_SPACE = ON

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

GO

 

USE SQLTAG

GO

 

CREATE DATABASE AUDIT SPECIFICATION [Test_Audit_DB]

FOR SERVER AUDIT [Test_Audit]

ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (DELETE ON DATABASE::[SQLTAG] BY [dbo]),

ADD (INSERT ON DATABASE::[SQLTAG] BY [dbo]),

ADD (UPDATE ON DATABASE::[SQLTAG] BY [dbo]),

ADD (SELECT ON DATABASE::[SQLTAG] BY [dbo])

WITH (STATE = ON)

GO

 

다음과 같이 SQLTAG 데이터베이스에 감사가 생성된 것을 확인 할 수 있다.

 

SQLTAG에 감사가 정상적으로 캡처 되는지 확인해 보자. 실습 방법은 테이블을 생성하여 해당 작업이 캡처되었는지 확인 한다.

USE SQLTAG

GO

 

CREATE TABLE TBL_B (COL1 INT)

GO

 

SELECT * FROM FN_GET_AUDIT_FILE('C:\SQL_DATA\*', DEFAULT, DEFAULT)

 

 

 

[SQLTAG] 데이터베이스를 백업 한다.

BACKUP DATABASE SQLTAG TO DISK = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK'

 

 

[SQLTAG] 데이터베이스의 백업 파일을 [SQLTAG2] 이름으로 복원 한다.

restore headeronly from disk = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK'

restore filelistonly from disk = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK'

 

RESTORE DATABASE SQLTAG2 FROM DISK = 'C:\SQL_BACKUP\SQLTAG_AUDIT.BAK' WITH RECOVERY,

MOVE 'SQLTAG' TO 'C:\SQL_DATA\SQLTAG.MDF',

MOVE 'SQLTAG_LOG' TO 'C:\SQL_DATA\SQLTAG_LOG.LDF'

 

복원 후 [SQLTAG2]의 감사를 살펴보면 기존의 감사 사양이 그대로 복원 된 것을 확인 할 수 있다.

 

새로 복원한 [SQLTAG2]의 경우 감사는 정의되어 있지만 서버 수준의 감사 정의는 SQLTAG2에 존재 하지 않는다. 따라서 감사 데이터가 캡처 되지 않는다. 이 문제를 해결하기 위하여 감사에 사용된 특정 GUID를 이용하여 서버 감사 명령을 실행 헤애 한다.

 

[기존 데이터베이스에 접근 할 수 있는 경우 GUID 추출하기] – 다음과 같이 SSMS에서 마우스 오른쪽을 클릭하여 스크립트 구문을 생성하면 GUID를 확인 할 수 있다.

 

[기존 데이터베이스에 접근 할 수 없는 경우 GUID 추출하기] – 새로 복원한 [SQLTAG2]에서 다음의 스크립트를 실행하여 GUID를 추출한다.

SELECT * FROM SYS.DATABASE_AUDIT_SPECIFICATIONS

 

 

GUID 추출이 완료 되었으면 다음 스크립트를 이용하여 감사를 설정한다. (기존 데이터베이스를 이용하여 스크립트를 생성한 경우 FILE_PATH를 변경하여 사용 할 수 있다.)

CREATE SERVER AUDIT [Test_Audit]

TO FILE

( FILEPATH = N'C:\SQL_Data2\'

,MAXSIZE = 100 MB

,MAX_ROLLOVER_FILES = 90

,RESERVE_DISK_SPACE = ON

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

,AUDIT_GUID = '1FA32A8E-9A82-4B0F-9C34-8B79826F62CF'

)

GO

 

이 작업이 완료 되면 데이터베이스가 감사 데이터를 수집을 시작 한다. 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp




No. Subject Author Date Views
1653 SSIS 패키지 보호 레벨 jevida(강성욱) 2016.01.11 3880
1652 DATABASE의 TABLE 및 COLUMN COMMENT 출력 joe 2015.09.10 7724
1651 백업 히스토리 모니터링 jevida(강성욱) 2015.03.31 11627
1650 백업 전략과 Differential Database Backup jevida(강성욱) 2015.03.31 11229
1649 SQL ServerPoint in time recovery jevida(강성욱) 2015.03.31 9056
1648 Striping SQL Server Database Backup jevida(강성욱) 2015.03.31 8697
» SQL 서버 감사가 있는 데이터베이스 복원 후 설정 jevida(강성욱) 2015.03.31 11790
1646 SQL ServerBACKUP 암호화 jevida(강성욱) 2015.03.30 8836
1645 SQL ServerErrorLog 관리 jevida(강성욱) 2015.03.30 10575
1644 SSMS 개체 탐색기를 통한 세부 정보 보기 jevida(강성욱) 2015.03.30 11592
1643 SSMS 접속했던 서버 목록 삭제 jevida(강성욱) 2015.03.30 12453
1642 SSMS 빠른 실행 아이콘 등록 하기 jevida(강성욱) 2015.03.30 8038
1641 SSMS 단축키 jevida(강성욱) 2015.03.27 7799
1640 SSMS UI 기능을 이용한 스크립트 생성 [1] jevida(강성욱) 2015.03.27 6886
1639 SSMS 서버 그룹 등록 및 서버 등록 jevida(강성욱) 2015.03.27 8664
1638 SSMS 여러 개체 선택하기 jevida(강성욱) 2015.03.27 5186
1637 SSMS 서버리스트 내보내기 / 가져오기 jevida(강성욱) 2015.03.27 6368
1636 SSMS 자동복구 활성 / 비활성 jevida(강성욱) 2015.03.27 4977
1635 SSMS 시스템 데이터베이스 숨기기 jevida(강성욱) 2015.03.27 4808
1634 SSMS 쿼리 자동 줄 바꿈 jevida(강성욱) 2015.03.27 9227





XE Login