DB를 개발하게 되면 일별 폴백업이 아닌 DB Script들만 따로 백업 받고 싶을 때가 있다. 이때 사용할 수 있는게 C#을 이용하면 아주 편하다.

꿈꾸는 거북이님의 스크립트를 이용해서 약간만 수정해서 만들어 보니 아주 편하다^^

 꿈꾸는 거북이님의 원본 글 

http://blog.naver.com/hrk007/60156735472

1. 소스

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.IO;
using System.Diagnostics.Eventing;

using Microsoft.SqlServer.Management.Sdk;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;

// ++ 참고 사이트
// http://blog.naver.com/PostView.nhn?blogId=hrk007&logNo=60156735472&categoryNo=0&parentCategoryNo=0&viewDate=¤tPage=1&postListTopCurrentPage=&userTopListOpen=true&userTopListCount=30&userTopListManageOpen=false&userTopListCurrentPage=1
// http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/smo-script-index-and-fk-s-on-a-database
// http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/

namespace DBManagement
{
    class DBScripting
    {
        static void Main(string[] args)
        {
            if (args.Length < 1)
            {
                throw new Exception("입력파라메터 필요 ex) 저장폴더 서버 db1,sp,table:db2,table");
            }
            
            string folder = args[0];
            Server myServer = new Server(args[1]);
            string[] databaseList = args[2].Split(new char[] { ':' });
            
            
            try
            {
                // 1. Using windows authentication
                myServer.ConnectionContext.LoginSecure = true;
                // 2. Using SQL Server authentication
                //myServer.ConnectionContext.LoginSecure = false;
                //myServer.ConnectionContext.Login = "";
                //myServer.ConnectionContext.Password = "";
                myServer.ConnectionContext.Connect();
                // GetSmoTest(myServer, databaseList, folder);
                getSMOObject(myServer, databaseList, folder);
            }
            catch (Exception ex)
            {
                insertWindowEventLog(ex.Message);
            }
            finally
            {
                if (myServer.ConnectionContext.IsOpen)
                    myServer.ConnectionContext.Disconnect();
            }
        } // end main


        // DB 내의 모든 SMO Object 정보 수집
        private static void getSMOObject(Server myServer, string[] databaseList, string folder)
        {
            //foreach (Database myDatabase in myServer.Databases)
            //{
            //    Console.WriteLine(myDatabase.Name);
            //}

            Urn[] urns = null;
            for (int i = 0; i < databaseList.Length; i++)
            {
                // init
                urns = null;
                // ibdb,table,sp
                string[] objList = databaseList[i].Split(new char[] {','});
                // 요청 DB name
                Database db = myServer.Databases[objList[0]];

                List list = new List();

                // 테이블
                for (int j = 1; j < objList.Length; j++)
                {
                    list = new List();
                    urns = null;

                    // 테이블
                    if (objList[j].ToUpper().Equals("TABLE"))
                    {
                        Scripter scripter = new Scripter(myServer);
                        scripter.Options.FileName = setFileName(folder, objList[0], objList[j]);                        
                        scripter.Options.AppendToFile = true;
                        scripter.Options.Indexes = true;
                        scripter.Options.ClusteredIndexes = true;
                        scripter.Options.ScriptSchema = true;
                        scripter.Options.NoCollation = true;
                        scripter.Options.DriDefaults = true;
                        scripter.Options.DriAllConstraints = true;                        
                        scripter.Options.ScriptDrops = false;

                        foreach (Table table in db.Tables)
                        {
                            if (!table.IsSystemObject)
                            {
                                //list.Add(table.Urn);
                                scripter.Script(new Urn[] { table.Urn });
                            }                            
                        }

                        //urns = list.ToArray();
                        //scripter.Script(urns);


                    }// if table


                    // SP
                    if (objList[j].ToUpper().Equals("SP"))
                    {
                        foreach (StoredProcedure sp in db.StoredProcedures)
                        {
                            if (!sp.IsSystemObject && !sp.IsEncrypted)
                            {
                                list.Add(sp.Urn);
                            }                            
                        }
                        urns = list.ToArray();
                        Scripter scripter = new Scripter(myServer);
                        scripter.Options.FileName = setFileName(folder, objList[0], objList[j]);
                        scripter.Options.AppendToFile = true;                        
                        scripter.Script(urns);
                    } // if sp

                    // View
                    if (objList[j].ToUpper().Equals("VIEW"))
                    {
                        foreach (View vw in db.Views)
                        {                            
                            if (!vw.IsSystemObject && !vw.IsEncrypted)
                            {
                                list.Add(vw.Urn);
                            }
                        }

                        urns = list.ToArray();
                        Scripter scripter = new Scripter(myServer);
                        scripter.Options.FileName = setFileName(folder, objList[0], objList[j]);
                        scripter.Options.AppendToFile = true;
                        scripter.Script(urns);
                    } // if sp                    

                    // function
                    if (objList[j].ToUpper().Equals("FUNC"))
                    {
                        foreach (UserDefinedFunction uf in db.UserDefinedFunctions)
                        {
                            if (!uf.IsSystemObject && !uf.IsEncrypted)
                            {
                                list.Add(uf.Urn);
                            }
                        }

                        urns = list.ToArray();
                        Scripter scripter = new Scripter(myServer);
                        scripter.Options.FileName = setFileName(folder, objList[0], objList[j]);
                        scripter.Options.AppendToFile = true;
                        scripter.Script(urns);
                    } // if sp    


                } // end object
                        
            } // for (database)
        }
 

        // 윈도우 Event Log 입력
        private static void insertWindowEventLog(string message)
        {
            try
            {
                System.Diagnostics.EventLog eventLog = new System.Diagnostics.EventLog();
                if (!System.Diagnostics.EventLog.SourceExists("DBObjectScript"))
                {
                    System.Diagnostics.EventLog.CreateEventSource("DBObjectScript", "Application");
                }
                eventLog.Source = "DBObjectScript";
                int eventID = 8;
                eventLog.WriteEntry(message,
                                    System.Diagnostics.EventLogEntryType.Error,
                                    eventID);
                eventLog.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
 

        // 파일명 생성 folderName = "D:\objectscript"
        private static string setFileName(string folderName, string dbname, string objectname)
        {
            string filename = "";
            // 디렉토리
            try
            {
                // 생성
                DirectoryInfo Dir = new DirectoryInfo(folderName);
                if (!Dir.Exists)
                {
                    Dir = Directory.CreateDirectory(folderName);
                }

                filename = folderName + "\\" + dbname + "_" + objectname + "_"
                            + DateTime.Now.ToString("yyyymmdd_hhmm") + ".sql";
                

                // 일자_시분초.sql
                //filename = folderName + "\\" + dbname + "_" + objectname + "_"
                //    + DateTime.Now.ToShortDateString().Replace(":","")
                //    + "_" + DateTime.Now.ToShortTimeString().Replace(":", "") + ".sql";
            }
            catch (Exception ex)
            {
                insertWindowEventLog(ex.Message);
            }
            finally
            {
                if (filename == "")
                  filename = folderName + "\\" + dbname + "_objectscript.sql";  
            }
            return filename;
            
        }
 

    }
}

2. 실행파일

아래의 실행 파일을 다운로드 받거나 위의 소스를 컴파일해서 사용한다.

DBScripting.zip

3. 사용방법

저장경로 DB서버주소 DB명 저장하려고 하는 개체

실행 예) 저장폴더  서버 DB,table,sp,view,func:DB,table 

4. SQL Agent JOB 등록

5. 스크립트 관리??

스크립트 비교 검증 어떻게 할까?? 나는 주로 WinMerge를 애용한다..

위와 같이 비교가 가능하다.