728x90

DB 서버를 관리할 때, 주기적인 데이터 풀백업 말고도 DB 테이블 변경 사항이 발생될 때마다 전체 테이블 스키마를 백업해야한다. 테이블 스키마를 덤프뜰때, GUI 툴인 Enterprise Manager의 "Generate SQL Script" 기능을 사용할 수도 있겠지만, DB 서버마다 그리고 데이터베이스마다 일일히 마우스 클릭한다는건 귀찮은 작업이다. 그리고 마우스 클릭 작업은 관리자가 인지 못하는 실수를 만들 위험도 높다.

EM에서 할 수 있는 작업이라면, 쿼리로도 가능하기에, 프로필로러를 돌린 상태에서 해당 작업을 실행하여 EM이 어떤 쿼리를 실행시키나 살펴봤더니, 여러개의 쿼리가 실행되는데 그 과정을 하나의 스크립트로 작성하기엔 정보가 부족했고, 지금은 퇴사하신 이전 DBA가 썼다는 스크립트도 있는데.. 그게 워낙 대하소설이라.. 차라리 그걸 참고해서 새로 짜는게 맘 편할 것 같았다.

DB 서버와 관리자 컴퓨터가 AD로 묶이지 않았다는 가정 아래, 로컬에서는 윈도우 인증으로, 원격에서는 SQL 서버 인증으로 실행될 수 있도록 짰다. 'config data' 부분만 환경에 맞게 변경해주면 된다. 스크립트의 자세한 내용을 알고 싶은 분은 맨위 주석문의 레퍼런스를 참고하시라.

-- 
-- programmer    ADDNULL
-- latest        2006/11/14
-- description    generate table create script
-- reference    http://support.microsoft.com/kb/152801/ko
--        http://msdn2.microsoft.com/ko-kr/library/ms187693.aspx
--        http://dbforums.com/showthread.php?t=362835
--
DECLARE
    @ServerAddr        VARCHAR(255),
    @ServerID        VARCHAR(255),
    @ServerPassword    VARCHAR(255),
    @DatabaseName        VARCHAR(255),
    @OutputFileName        VARCHAR(255)
 
DECLARE
    @ObjectServer    INT,
    @ObjectTransfer    INT,
    @ObjectDB    INT,
    @ObjectItem    INT
 
DECLARE
    @SQLDMOScript_Default            INT,
    @SQLDMOScript_ObjectPermissions    INT,
    @SQLDMOScript_Indexes            INT,
    @ScriptOption                INT
 
-- config data
SET @ServerAddr = '.'    -- server IP address, port (ex : '127.0.0.1,1433')
SET @ServerID = null    -- user's ID for remote sql authentication (ex : 'sa')
SET @ServerPassword = null    -- user's password for remote sql authentication
SET @DatabaseName = null    -- database name to generate script
SET @OutputFileName = 'c:\table.sql'    -- output file name
 
-- connect to server
EXEC sp_OACreate 'SQLDMO.SQLServer', @ObjectServer OUTPUT
EXEC sp_OACreate 'SQLDMO.Transfer', @ObjectTransfer OUTPUT
IF (@ServerID IS NULL OR @ServerPassword IS NULL) BEGIN
    -- local (windows authentication)
    EXEC sp_OASetProperty @ObjectServer, 'LoginSecure', 'True'
    EXEC sp_OAMethod @ObjectServer, 'Connect', null, @ServerAddr
END ELSE BEGIN
    -- remote (sql authentication)
    EXEC sp_OAMethod @ObjectServer, 'Connect', null, @ServerAddr, @ServerID, @serverPassword
END
 
-- get a database object
EXEC sp_OAGetProperty @ObjectServer, 'Databases', @ObjectDB OUTPUT
EXEC sp_OAMethod @ObjectDB, 'Item', @ObjectItem OUTPUT, @DatabaseName
 
-- set object property
SET @SQLDMOScript_Default = 4    
SET @SQLDMOScript_ObjectPermissions = 2  
SET @SQLDMOScript_Indexes = 73736
SET @ScriptOption = @SQLDMOScript_Default | @SQLDMOScript_ObjectPermissions | @SQLDMOScript_Indexes
EXEC sp_OASetProperty @ObjectTransfer, 'CopyAllTables', 1
EXEC sp_OASetProperty @ObjectTransfer, 'ScriptType', @ScriptOption
 
-- output
EXEC sp_OAMethod @ObjectItem, 'ScriptTransfer', NULL, @ObjectTransfer, 2, @OutputFileName
 
-- destory objects
EXEC sp_OADestroy @ObjectItem
EXEC sp_OADestroy @ObjectDB
EXEC sp_OADestroy @ObjectTransfer
EXEC sp_OADestroy @ObjectServer
[출처] [펌] MSSQL 모든 Table Schema를 구하는 스크립트|작성자 주디

'데이터베이스 > SQL Server' 카테고리의 다른 글

INFORMATION 스키마 뷰 생성  (0) 2008.05.08
사용자 계정 관리  (0) 2008.05.08
쿼리 테크닉 - 1부  (0) 2008.05.07
쿼리 테크닉 - 2부  (0) 2008.05.07
SQL Sever에 별칭(Alias)을 달아보자  (0) 2008.05.07

+ Recent posts

티스토리툴바