Post reply

Automated GEN SQL Script method?

  • April 4, 2005 at 9:41 am

    #85777

     I know how to manually GENERATE SQL Script for all objects in a Database (using E/M, right mouse-clicking a DB, Generate SQL Script, then checkmarking all boxes, etc.)

    Is there a way to accomplish this automatically - to schedule a SQL Server Job to Automatically script all objects in a database?

    A Backup & Restore is not an option.   The lead Developer wants an automated, nitely mechanism to dynamically script out Database "A" -- which inherits all changes to structures -- then replace Database "B" using the auto-generated Script.  This to accomodate an .MSI type rollout.

    thx in advance-

    BT

  • April 4, 2005 at 10:09 am

    #550369

    Pulled from the following link: http://www.karaszi.com/SQLServer/info_generate_script.asp

     

    The SQLDMO API (DDL only)

    The scrip generation code used by Enterprise Manager and Query Analyzer is available for us to use in the SQLDMO API. This means that you can write your own applets to generate scripts, and have the script looking exactly as you want. Below you find a simple VB example which generates script for all objects in the database:

    Dim oSS As SQLDMO.SQLServer

    Dim oDb As SQLDMO.Database

    Dim oT As SQLDMO.Transfer

    Dim sS As String

    Sub Script()

    Set oSS = New SQLDMO.SQLServer

    Set oT = New SQLDMO.Transfer

    oSS.Connect "server", "login", "password" 'Connect to the server

    Set oDb = oSS.Databases("pubs") 'Use a DB

    oT.CopyAllTables = True

    oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"

    End Sub

    You can also generate script using DMO per object. Just create an instance of the object and use the script method for that object.

    Scptxfr.exe (DDL only)

    This tool comes with SQL Server. See below URL for more information:

    http://www.support.microsoft.com/?id=220163

    They also listed some other products at that URL I listed at the top. You should probably review it in whole. I hope this helps.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply