MSSQL DML 로그 저장 설정

1. 감사 사양 설정 및 실행

-- 서버 수준에서 감사 사양 생성
USE master
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'D:\MSSQL\AuditLogs\');

-- 서버 감사 사양을 시작
USE master
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

-- 데이터베이스 수준에서 감사 사양 생성
use MYDATABASENAME
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
FOR SERVER AUDIT ServerAudit
ADD (INSERT, UPDATE, DELETE ON DATABASE::MYDATABASENAME BY PUBLIC);

-- 데이터베이스 감사 사양을 시작
use MYDATABASENAME
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
WITH (STATE = ON);

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

2. 감사 사양 종료 및 삭제

-- 감사 사양 종료 및 삭제
USE master
ALTER SERVER AUDIT ServerAudit
WITH (STATE = OFF);

-- 서버 감사 사양 삭제
USE master
DROP SERVER AUDIT ServerAudit;

-- 데이터베이스 감사 사양을 종료
use MYDATABASENAME
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
WITH (STATE = OFF);

-- 데이터베이스 감사 사양 삭제
use MYDATABASENAME
DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpec;

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

3. 감사 사양 로그 조회

-- 감사 로그 파일 조회 (로컬 시간으로 변환)
SELECT 
    DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), event_time) AS local_event_time,
    server_instance_name,
    database_name,
    schema_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('D:\MSSQL\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT);

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

4. 에이전트 작업 스케쥴 등록 :  매일 오전 00:00 신규 파일 생성

작업명 : DailyAuditLogRotation

-- T-SQL 작성
DECLARE @currentDateTime NVARCHAR(50);
DECLARE @auditFilePath NVARCHAR(500);
DECLARE @sqlCmd NVARCHAR(MAX);
DECLARE @innerSqlCmd NVARCHAR(MAX);

-- 현재 시간 문자열 생성
SET @currentDateTime = REPLACE(CONVERT(NVARCHAR, GETDATE(), 120), ':', '');

-- 감사 로그 파일 경로 설정 (예: D:\MSSQL\AuditLogs\AuditLog_YYYYMMDD_HHMMSS.audit)
SET @auditFilePath = 'D:\MSSQL\AuditLogs\AuditLog_' + @currentDateTime + '.audit';

-- 새로운 감사 로그 파일 생성을 위한 동적 SQL 명령 준비
SET @innerSqlCmd = '
    ALTER SERVER AUDIT ServerAudit
    WITH (STATE = OFF);

    ALTER SERVER AUDIT ServerAudit
    WITH (STATE = ON);
';

-- 동적 SQL 명령 실행
EXEC sp_executesql @innerSqlCmd;