기본 추적을 사용한 SQL Server 스키마 변경사항 캡처

기본 추적을 사용한 SQL Server 스키마 변경사항 캡처

 

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

 

SQL Server에서 스키마 변경이 발생하였을 때 이를 캡처하고 확인할 수 있는 방법에 대해서 알아본다.

 

기본적으로 SSMS에서는 스키마 변경 기록 보고서를 제공한다. 이 보고서에서는 DDL 작업에 대한 내용과 시간, 로그인 사용자, 사용자 이름 등 유용한 정보가 표시되지만 SQL Server가 시작된 이후의 내용만 확인할 수 있다.

 

 

 

 

SQL Server가 다시 시작되더라도 변경 내용을 히스토리로 관리할 수 있도록 사용자 테이블에 변경 내역을 저장하는 방법에 대이다. 우선 변경 내용을 저장할 테이블을 생성한다.

-- create table to hold DDL history CREATE TABLE sw_test.[dbo].[DDL_History](     [database_name] [nvarchar](256) NULL,     [start_time] [datetime] NULL,     [login_name] [nvarchar](256) NULL,     [user_name] [nvarchar](256) NULL,     [application_name] [nvarchar](256) NULL,     [ddl_operation] [nvarchar](40) NULL,     [object] [nvarchar](257) NOT NULL,     [type_desc] [nvarchar](60) NULL ) ON [PRIMARY] GO

 

아래 스크립트를 실행하면 변경된 내역을 위에 생성한 사용자 테이블로 저장한다.

USE [master] GO --Declare variables SET NOCOUNT ON; DECLARE @dbid INT; DECLARE @dbname VARCHAR(100); DECLARE @execstr VARCHAR(4000); -- Declare a cursor. DECLARE dbs CURSOR FOR SELECT database_id,name from sys.databases where name not in ('master','tempdb');   -- Open the cursor. OPEN dbs; -- Loop through all the tables in the database. FETCH NEXT FROM dbs INTO @dbid,@dbname; WHILE @@FETCH_STATUS = 0 BEGIN;   -- Check default trace for any DDL in specific database SELECT @execstr='if (select convert(int,value_in_use) from sys.configurations where name = ''default trace enabled'') = 1 begin declare @d1 datetime; declare @diff int; declare @curr_tracefilename varchar(500); declare @base_tracefilename varchar(500); declare @indx int ; declare @temp_trace table ( obj_name nvarchar(256) , obj_id int , database_name nvarchar(256) , start_time datetime , event_class int , event_subclass int , object_type int , server_name nvarchar(256) , login_name nvarchar(256) , user_name nvarchar(256) , application_name nvarchar(256) , ddl_operation nvarchar(40) );   select @curr_tracefilename = path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename) select @indx = PATINDEX(''%\%'', @curr_tracefilename) set @curr_tracefilename = reverse(@curr_tracefilename) set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';   insert into @temp_trace select ObjectName , ObjectID , DatabaseName , StartTime , EventClass , EventSubClass , ObjectType , ServerName , LoginName , NTUserName , ApplicationName , ''temp'' from ::fn_trace_gettable( @base_tracefilename, default ) where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = '+cast(@dbid as varchar)+' ;   update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46; update @temp_trace set ddl_operation = ''DROP'' where event_class = 47; update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;   select @d1 = min(start_time) from @temp_trace set @diff= datediff(hh,@d1,getdate()) set @diff=@diff/24;           insert into sw_test.dbo.DDL_History (database_name,start_time,login_name,user_name,application_name,ddl_operation,object,type_desc)         select database_name,start_time,login_name,user_name,application_name,ddl_operation,s.name+''.''+o.name as "object",o.type_desc         from @temp_trace tt inner join          '+@dbname+'.sys.objects o on tt.obj_id=o.object_id inner join              '+@dbname+'.sys.schemas s on s.schema_id=o.schema_id      where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise and start_time > (select case when max(start_time) is NULL then ''1900-01-01'' else max(start_time) end from sw_test.dbo.DDL_History) end'   EXEC (@execstr)   FETCH NEXT FROM dbs INTO @dbid,@dbname; END; -- Close and deallocate the cursor. CLOSE dbs; DEALLOCATE dbs;

 

 

위 스크립트를 주기적으로 실행하도록 설정하여 DDL 변경 사항에 대해서 이력관리를 할 수 있도록 한다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4057/capture-sql-server-schema-changes-using-the-default-trace/

 

2015-10-21 / 강성욱 / http://sqlmvp.kr

 

MS SQL, SQL Server, 스키마 변경, DDL 캡처, DB 보안, DB 접근, DDL 사용자 확인, DB 변경 이력 관리