Scripting Database Objects in SQL 2005

  • Is there a way to script the database objects and store them in a file. I know how to script through wizard but I want to automate this process.

  • Hi,

    I have come accross this script, please check if this helps. Download the script available at the below location

    http://www.codeproject.com/KB/database/SQL_DB_DOCUMENTATION.aspx

    Then we can do the necessary changes and automate the process through a SQL job.

    -Rajini

  • Thanks for the info. I am using SQL - DMO script method which is much better. here is the code.

    This procedure will create individual table scripts.

    you can also create jobs,stored procedure,views, alerts.

    CREATE PROCEDURE [dbo].[SP_GENERATE_TABLE_SCRIPT]

    @DATABASENAME VARCHAR(100),

    @PATH VARCHAR(500)

    AS

    /*

    SP_GENERATE_TABLE_SCRIPT 'database Name','C:\TEMP\'

    */

    DECLARE @oServer int

    DECLARE @method varchar(300)

    DECLARE @TSQL varchar(4000)

    DECLARE @ScriptType int

    DECLARE @TABLENAME VARCHAR(100)

    DECLARE @SERVERNAME VARCHAR(100)

    SELECT @SERVERNAME = @@servername

    EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT

    EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'

    EXEC sp_OAMethod @oServer, 'Connect', NULL, @SERVERNAME

    SET @ScriptType =1|4|32|64|262144

    DECLARE TABLECURSOR CURSOR FOR

    SELECT [NAME] FROM SYS.OBJECTS WHERE TYPE ='U'

    OPEN TABLECURSOR

    FETCH NEXT FROM TABLECURSOR INTO @TABLENAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @method = 'Databases("'+@DATABASENAME+'").' +

    'Tables("'+@TABLENAME+'").Script' +

    '(' + CAST (@ScriptType AS CHAR) +

    ',"'+@PATH+''+@TABLENAME+'.sql")'

    FETCH NEXT FROM TABLECURSOR INTO @TABLENAME

    EXEC sp_OAMethod @oServer, @method , @TSQL OUTPUT

    END

    CLOSE TABLECURSOR

    DEALLOCATE TABLECURSOR

    EXEC sp_OADestroy @oServer

  • Hi,

    EXEC dbo.SP_GENERATE_TABLE_SCRIPT 'TESTDB','C\TempTest'

    I ran this but did not get anything in 'C\TempTest'

  • balbirsinghsodhi (8/26/2008)


    Is there a way to script the database objects and store them in a file. I know how to script through wizard but I want to automate this process.

    Balbir, have a look at http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31460/[/url].

    I run this script every day with a Windows Scheduled Task to get all my objects in VSS. If you do not have VSS, you could turn off the calls to VSS quite easily, and still end up with a folder structure with all your objects scripted. Tested on 2000, if you are using 2005, your mileage may vary.

    HTH

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • Can you give me the bit detail how did you added scripts into vss,i have vss and mssql 2005 also.

    Thanks

    Raju

  • From the script header 😉

    ' Instructions.

    ' To use this script, you need to create a project in VSS & create various sub projects.

    '

    ' -/$

    ' |-Databases

    ' |-pubs

    ' |-Defaults

    ' |-Rules

    ' |-StoredProcedures

    ' |-Tables

    ' |-UserDefinedDataTypes

    ' |-UserDefinedFunctions

    ' |-Views

    '

    '

    ' You then need to create a batch file to pass in the required parameters.

    '

    '@Echo Off

    '::SQLServer1

    'cscript dbtovss2.vbs -w C:\Temp\pubs -r $/Databases/pubs/ -i \\VSSServer\VSS\Data\srcsafe.ini -u Admin -p secret -s SQLServer1 -d pubs

    '::call it again for a different SQL db

    'cscript dbtovss2.vbs -w C:\Temp\dbname2 -r $/Databases/dbname2/ -i \\VSSServer\VSS\Data\srcsafe.ini -u Admin -p secret -s SQLServer1 -d dbname2

    '::End of Batch file

    '

    ' This needs to run on a PC with both a VSS & SQL client installed, as well as having TLBINF32.DLL registered.

    ' To register TLBINF32.DLL, open a command line in the folder you have copied TLBINF32.DLL to and type:

    '

    ' Regsvr32 TLBINF32.DLL

    '

    ' TLBINF32.DLL is shipped on the Visual Studio 6.0 and Visual Basic 6.0 CDs.

    ' According to Microsoft it is redistributable,

    ' so look for it at http://glossopian.co.uk/Uploads/Main/TLBINF32.zip.

    '

    ' If you create the structure above, this script will script off the pubs db 'out of the box'


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • Raju..

    You are missing back slash in the path folder. just add back slash and you will be fine. It should be like this.

    EXEC dbo.SP_GENERATE_TABLE_SCRIPT 'TESTDB','C\TempTest\'

  • Thanks SSC Veteran, a very useful script! However this script do not save all information about the table. The missing parts are triggers and extended properties (MS_Description for instance). If you could add these parts to script it would be more useful ;).

  • Copy and paste these following three stored procedure and you will get everything. make sure these SP's should be in master database. after pasting these stored procedure you can run like this. change the path and also there should be c:\temp directory on your system. this will script all the objects like defaults, functions,indexes,procedures,rules, tables, triggers and views.

    execdbo.DBMNT_ScriptAllDatabases

    @SourceUID= null ,

    @SourcePWD= null ,

    @OutFilePath= 'k:\SQL_Backups\Script\' ,

    @OutFileName= null ,

    @WorkPath= 'c:\temp\' ,-- no spaces

    @SourceSVR= null,

    @Database= null

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    CREATE procedure [dbo].[DBMNT_ScriptAllDatabases]

    @SourceUIDvarchar(128) ,-- null for trusted connection

    @SourcePWDvarchar(128) ,

    @OutFilePathvarchar(256) ,-- Root path - will add directory for object types

    @OutFileNamevarchar(128) , -- null for separate file per object script

    @WorkPathvarchar(256) ,

    @SourceSVRvarchar(128) = null ,-- to script remote server

    @Databasevarchar(128) = null-- to script single database / jobs / DTS

    as

    /*

    execDBMNT_ScriptAllDatabases

    @SourceUID= null ,

    @SourcePWD= null ,

    @OutFilePath= 'c:\a\' ,

    @OutFileName= null ,

    @WorkPath= 'c:\temp\' ,-- no spaces

    @SourceSVR= null

    execDBMNT_ScriptAllDatabases

    @SourceUID= null ,

    @SourcePWD= null ,

    @OutFilePath= 'c:\a\' ,

    @OutFileName= null ,

    @WorkPath= 'c:\temp\' ,-- no spaces

    @SourceSVR= null ,

    @Database= 'JOBS'

    execDBMNT_ScriptAllDatabases

    @SourceUID= null ,

    @SourcePWD= null ,

    @OutFilePath= 'c:\a\' ,

    @OutFileName= null ,

    @WorkPath= 'c:\temp\' ,-- no spaces

    @SourceSVR= null ,

    @Database= 'DTS'

    execDBMNT_ScriptAllDatabases

    @SourceUID= null ,

    @SourcePWD= null ,

    @OutFilePath= 'c:\a\' ,

    @OutFileName= null ,

    @WorkPath= 'c:\temp\' ,-- no spaces

    @SourceSVR= null ,

    @Database= 'msdb'

    */

    declare @sql varchar(1000) ,

    @cmdvarchar(1000)

    if @SourceSVR is null

    begin

    select @SourceSVR = @@servername

    end

    if right(@OutFilePath,1) <> '\'

    begin

    select @OutFilePath = @OutFilePath + '\'

    end

    if right(@WorkPath,1) <> '\'

    begin

    select @WorkPath = @WorkPath + '\'

    end

    select@OutFilePath = @OutFilePath + '"' + @SourceSVR + '"'

    exec master..xp_cmdshell @cmd

    select@OutFilePath = @OutFilePath + '\'

    select @sql =

    'selectname

    from[' + @SourceSVR + '].master.dbo.sysdatabases

    wherename <> ''tempdb'''

    if @Database is not null

    begin

    select @sql = @sql + ' and name = ''' + @Database + ''''

    end

    create table #tblDatabases (name varchar(128))

    insert#tblDatabases

    (name)

    exec (@sql)

    declare@FilePathvarchar(256)

    declare@namevarchar(128) ,

    @maxnamevarchar(128)

    select@name = '' ,

    @maxname = max(name)

    from#tblDatabases

    while @name < @maxname

    begin

    select@name = min(name) from #tblDatabases where name > @name

    select@FilePath = @OutFilePath + '"' + @name + '"'

    -- output current database name

    select CurrentDatabase = @name

    -- create output directory - will fail if already exists but ...

    select@cmd = 'mkdir ' + @FilePath

    exec master..xp_cmdshell @cmd, no_output

    execDBMNT_ScriptAllObjectsInDatabase

    @SourceDB = @name ,

    @SourceUID = @SourceUID ,

    @SourcePWD = @SourcePWD ,

    @OutFilePath = @FilePath ,

    @OutFileName = @OutFileName ,-- null for separate file per object script

    @WorkPath= @WorkPath ,

    @SourceSVR= @SourceSVR

    end

    if coalesce(@Database, 'JOBS') = 'JOBS'

    begin

    select@FilePath = @OutFilePath + 'JOBS'

    -- create output directory - will fail if already exists but ...

    select@cmd = 'mkdir ' + @FilePath

    exec master..xp_cmdshell @cmd, no_output

    execDBMNT_ScriptObjects

    @SourceDB= 'msdb' ,

    @SourceObject= null ,-- null for all objects

    @SourceUID = @SourceUID ,

    @SourcePWD = @SourcePWD ,

    @OutFilePath = @FilePath ,

    @OutFileName = @OutFileName ,-- null for separate file per object script

    @ObjectType= 'JOBS' ,

    @WorkPath= @WorkPath ,

    @SourceSVR= @SourceSVR

    end

    if coalesce(@Database, 'DTS') = 'DTS'

    begin

    select@FilePath = @OutFilePath + 'DTS'

    -- create output directory - will fail if already exists but ...

    select@cmd = 'mkdir ' + @FilePath

    exec master..xp_cmdshell @cmd, no_output

    execDBMNT_ScriptObjects

    @SourceDB= 'msdb' ,

    @SourceObject= null ,-- null for all objects

    @SourceUID = @SourceUID ,

    @SourcePWD = @SourcePWD ,

    @OutFilePath = @FilePath ,

    @OutFileName = @OutFileName ,-- null for separate file per object script

    @ObjectType= 'DTS' ,

    @WorkPath= @WorkPath ,

    @SourceSVR= @SourceSVR

    end

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

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    CREATE procedure [dbo].[DBMNT_ScriptAllObjectsInDatabase]

    @SourceDBvarchar(128) ,

    @SourceUIDvarchar(128) ,-- null for trusted connection

    @SourcePWDvarchar(128) ,

    @OutFilePathvarchar(256) ,-- Root path - will add directory for object types

    @OutFileNamevarchar(128) , -- null for separate file per object script

    @WorkPathvarchar(256) ,

    @SourceSVRvarchar(128)

    as

    if right(@OutFilePath,1) <> '\'

    begin

    select @OutFilePath = @OutFilePath + '\'

    end

    if right(@WorkPath,1) <> '\'

    begin

    select @WorkPath = @WorkPath + '\'

    end

    set nocount on

    declare @tblObjectType table (ObjectType varchar(50))

    insert@tblObjectType select 'PROCEDURES'

    insert@tblObjectType select 'FUNCTIONS'

    insert@tblObjectType select 'TABLES'

    insert@tblObjectType select 'VIEWS'

    insert@tblObjectType select 'INDEXES'

    insert@tblObjectType select 'TRIGGERS'

    insert@tblObjectType select 'DEFAULTS'

    insert@tblObjectType select 'RULES'

    declare@FilePathvarchar(256) ,

    @cmdvarchar(1000)

    declare@ObjectTypevarchar(50) ,

    @maxObjectTypevarchar(50)

    select@ObjectType = '' ,

    @maxObjectType = max(ObjectType)

    from@tblObjectType

    while @ObjectType < @maxObjectType

    begin

    select@ObjectType = min(ObjectType) from @tblObjectType where ObjectType > @ObjectType

    select@FilePath = @OutFilePath + @ObjectType

    -- create output directory - will fail if already exists but ...

    select@cmd = 'mkdir ' + @FilePath

    exec master..xp_cmdshell @cmd, no_output

    execDBMNT_ScriptObjects

    @SourceDB = @SourceDB ,

    @SourceObject = null ,

    @SourceUID = @SourceUID ,

    @SourcePWD = @SourcePWD ,

    @OutFilePath = @FilePath ,

    @OutFileName = null ,-- null for separate file per object script

    @ObjectType = @ObjectType ,

    @WorkPath= @WorkPath ,

    @SourceSVR= @SourceSVR

    end

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

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    CREATE procedure [dbo].[DBMNT_ScriptObjects]

    @SourceDBvarchar(128) ,

    @SourceObjectvarchar(128) ,-- null for all objects

    @SourceUIDvarchar(128) ,-- null for trusted connection

    @SourcePWDvarchar(128) ,

    @OutFilePathvarchar(256) ,

    @OutFileNamevarchar(128) , -- null for separate file per object script

    @ObjectTypevarchar(50) ,-- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES

    @WorkPathvarchar(256) ,

    @SourceSVRvarchar(128)

    as

    /*

    exec DBMNT_ScriptObjects

    @SourceDB= 'TradarBe' ,

    @SourceObject= 'tbl_CQS_Pricing_BloombergData' ,-- null for all objects

    @SourceUID= null ,-- null for trusted connection

    @SourcePWD= null ,

    @OutFilePath= 'c:\a\' ,

    @OutFileName= null , -- null for separate file per object script

    @ObjectType= 'TABLES' ,-- PROCS, FUNCTIONS, TABLES, VIEWS, INDEXES

    @WorkPath= 'c:\temp\' ,

    @SourceSVR= 'SVR01'

    */

    set nocount on

    declare@ScriptTypeint ,

    @FileNamevarchar(256) ,

    @tmpFileNamevarchar(256) ,

    @buffervarchar(8000) ,

    @collectionvarchar(128) ,

    @idint ,

    @namevarchar(128) ,

    @subnamevarchar(128)

    declare@contextvarchar(255) ,

    @sqlvarchar(1000) ,

    @rcint

    if right(@OutFilePath,1) <> '\'

    begin

    select @OutFilePath = @OutFilePath + '\'

    end

    if right(@WorkPath,1) <> '\'

    begin

    select @WorkPath = @WorkPath + '\'

    end

    select@SourceDB = replace(replace(@SourceDB,'[',''),'[','')

    select@ScriptType= 4 | 1 | 64 ,

    @FileName= @OutFilePath + @OutFileName ,

    @tmpFileName= @WorkPath + 'ScriptTmp.txt'

    declare@objServerint ,

    @objTransferint ,

    @strResultvarchar(255) ,

    @strCommandvarchar(255)

    -- get objects to script and object type

    create table #Objects (name varchar(128), subname varchar(128) default null, id int identity(1,1))

    if @SourceObject is not null

    begin

    insert#Objects

    (name)

    select @SourceObject

    end

    if @ObjectType = 'TABLES'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select TABLE_NAME, null '

    select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.TABLES '

    select @sql = @sql + 'whereTABLE_TYPE = ''BASE TABLE'''

    end

    select @collection = 'tables'

    end

    else if @ObjectType in ('PROCS', 'PROCEDURES')

    begin

    if @SourceObject is null

    begin

    select @sql = 'select ROUTINE_NAME, null '

    select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '

    select @sql = @sql + 'whereROUTINE_TYPE = ''PROCEDURE'''

    end

    select @collection = 'storedprocedures'

    end

    else if @ObjectType = 'FUNCTIONS'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select ROUTINE_NAME, null '

    select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.ROUTINES '

    select @sql = @sql + 'whereROUTINE_TYPE = ''FUNCTION'''

    end

    select @collection = 'userdefinedfunctions'

    end

    else if @ObjectType = 'VIEWS'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select TABLE_NAME, null '

    select @sql = @sql + 'from[' + @SourceDB + '].INFORMATION_SCHEMA.VIEWS '

    select @sql = @sql + 'whereTABLE_NAME not like ''sys%'''

    end

    select @collection = 'views'

    end

    else if @ObjectType = 'INDEXES'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select o.name, i.name '

    select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysindexes i '

    select @sql = @sql + 'whereo.type = ''U'' '

    select @sql = @sql + 'and i.id = o.id and i.indid <> 0 '

    select @sql = @sql + 'and i.name not like ''_WA_%'''

    select @sql = @sql + 'and o.name not like ''dtprop%'''

    select @sql = @sql + 'and i.name not in (select name from [' + @SourceDB + ']..sysobjects)'

    end

    select @collection = 'tables'

    end

    else if @ObjectType = 'TRIGGERS'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select o2.name, o.name '

    select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o, [' + @SourceDB + ']..sysobjects o2 '

    select @sql = @sql + 'whereo.xtype = ''TR'' '

    select @sql = @sql + 'ando.parent_obj = o2.id '

    end

    select @collection = 'tables'

    end

    else if @ObjectType = 'DEFAULTS'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select o.name, null '

    select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o '

    select @sql = @sql + 'where o.type = ''D'' and o.parent_obj = ''0'''

    end

    select @collection = 'Defaults'

    end

    else if @ObjectType = 'RULES'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select o.name, null '

    select @sql = @sql + 'from[' + @SourceDB + ']..sysobjects o '

    select @sql = @sql + 'where type = ''R'''

    end

    select @collection = 'Rules'

    end

    else if @ObjectType = 'JOBS'

    begin

    if @SourceObject is null

    begin

    select @sql = 'select j.name, null '

    select @sql = @sql + 'frommsdb..sysjobs j '

    end

    select @collection = 'jobs'

    end

    else if @ObjectType = 'DTS'

    begin

    select@sql = 'dtsrun /NScript_DTS_Packages /S(local) /E '

    + '/A"ServerName":8="' + @SourceSVR + '" '

    + '/A"Path":8="' + @OutFilePath + '" '

    + '/A"UserName":8="' + coalesce(@SourceUID,'') + '" '

    + '/A"Password":8="' + coalesce(@SourcePWD,'') + '" '

    exec master..xp_cmdshell @sql

    return

    end

    else

    begin

    select 'invalid @ObjectType'

    return

    end

    if @SourceSVR <> @@servername

    begin

    select @sql = replace(@sql,'''','''''')

    insert#Objects (name, subname) exec ('select * from openquery(' + @SourceSVR + ',''' + @sql + ''')')

    end

    else

    begin

    insert#Objects (name, subname) exec (@sql)

    end

    -- create empty output file

    if @OutFileName is not null

    begin

    select@sql = 'echo. > ' + @FileName

    exec master..xp_cmdshell @sql

    end

    -- prepare scripting object

    select @context = 'create dmo object'

    exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    if @SourceUID is null

    begin

    select @context = 'set integrated security ' + @SourceSVR

    exec @rc = sp_OASetProperty @objServer, LoginSecure, 1

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    end

    select @context = 'connect to server ' + @SourceSVR

    exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    select @context = 'scripting'

    -- Script all the objects

    select @id = 0

    while exists (select * from #Objects where id > @id)

    begin

    select@id = min(id) from #Objects where id > @id

    select @name = name, @subname = subname from #Objects where id = @id

    if @OutFileName is null

    begin

    select@FileName = @OutFilePath + 'dbo."' + @name + coalesce('[' + @subname + ']','') + '.sql"'

    select@sql = 'echo. > ' + @FileName

    exec master..xp_cmdshell @sql

    end

    --select @sql = 'echo print ''Create = dbo.[' + @name + ']'+ coalesce('[' + @subname + ']','') + ''' >> ' + @FileName

    --exec master..xp_cmdshell @sql

    if @ObjectType = 'INDEXES'

    begin

    Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @name + '").indexes("' + @subname + '").script'

    end

    else if @ObjectType = 'TRIGGERS'

    begin

    Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @name + '").triggers("' + @subname + '").script'

    end

    else if @ObjectType = 'JOBS'

    begin

    Set @sql = 'Jobserver.Jobs("' + @name + '").Script'

    end

    else

    begin

    Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @name + '").script'

    end

    exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType , @tmpFileName

    select @sql = 'type ' + @tmpFileName + ' >> ' + @FileName

    exec master..xp_cmdshell @sql

    end

    -- delete tmp file

    select @sql = 'del ' + @tmpFileName

    exec master..xp_cmdshell @sql, no_output

    -- clear up dmo

    exec @rc = sp_OAMethod @objServer, 'Disconnect'

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    exec @rc = sp_OADestroy @objServer

    if @rc <> 0 or @@error <> 0 goto ErrorHnd

    -- clear up temp table

    drop table #Objects

    return

    ErrorHnd:

    select 'fail', @context

  • Hi

    I have a new problem

    Table1

    Have 3 Cols.

    COL1 COL2 COL3

    aa 11 111

    aa 11 111

    bb 22 222

    bb 22 222

    bb 22 222

    bb 22 222

    cc 33 333

    cc 33 333

    cc 33 333

    cc 33 333

    Now by combination on COL1 and COL2 if COL3 Value is same then it will set COL3 value 0

    COL1 COL2 COL3

    aa 11 111

    aa 11 0

    bb 22 222

    bb 22 0

    bb 22 0

    bb 22 0

    cc 33 333

    cc 33 0

    cc 33 0

    cc 33 0

    I have more than 1 lak record for this case,so we sud not use curson,loop.

    Please give me a solution without cursor and loop

    Raju

  • I ran into this situation a while back, and I started using code / procedures similar to what has been presented so far. I then decided to look at SMO and all that it had to offer. I ended up creating an SSIS package with two string variables and added a script task. The variables hold the server / instance name (vcSrvr) and the database (vcDB) you wish to script. You could wrap the script task in a FOR loop to walk through several databases, or just enter the single database in the variable and run it.

    The following script was what I worked out over time. I'm sure that I grabbed parts and pieces of this from various articles and BLOG postings, but unfortunately I don't have notes on any of that so I can not give the proper credit to those that published knowledge that helped with this.

    The script task will create text file(s) in the root directory of the C drive that contains all of the SQL code to reproduce the database and its objects / schema's. The file name is based on the server name and database name, so by walking through multiple databases on a server you will create individual files for each.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.SqlServer.Management.Common

    Imports Microsoft.SqlServer.Management.Smo

    Imports System.Collections.Specialized

    Public Class ScriptMain

    Public Sub Main()

    Dim sSrvr As String

    Dim sDB As String

    Dim sScriptFile As String

    Dim sCol As StringCollection

    Dim sLn As String

    If (Dts.Variables.Contains("vcSrvr") = False) Or _

    (Dts.Variables.Contains("vcDB") = False) Then

    Dts.TaskResult = Dts.Results.Failure

    End If

    sSrvr = CType(Dts.Variables("vcSrvr").Value, String)

    sDB = CType(Dts.Variables("vcDB").Value, String)

    Dim conn As New ServerConnection

    conn.ServerInstance = sSrvr

    conn.LoginSecure = False

    conn.Login = "{user with admin permissions}"

    conn.Password = "{appropriate password}"

    Dim oSrvr As Server = New Server(conn)

    Dim dbVar As Database = oSrvr.Databases(sDB)

    sScriptFile = "c:\" & oSrvr.InstanceName & "_" & dbVar.Name & ".sql"

    Dim sWriter As IO.StreamWriter = New IO.StreamWriter(sScriptFile, False)

    sCol = dbVar.Script()

    For Each sLn In sCol

    sWriter.WriteLine(sLn)

    Next

    sWriter.Flush()

    Dim xFer As Transfer

    xFer = New Transfer(dbVar)

    xFer.Options.WithDependencies = True

    xFer.Options.DriAll = True

    xFer.Options.ContinueScriptingOnError = True

    xFer.Options.IncludeIfNotExists = True

    xFer.CreateTargetDatabase = True

    xFer.CopyAllObjects = True

    xFer.DropDestinationObjectsFirst = True

    xFer.CopySchema = True

    xFer.CopyData = False

    xFer.DestinationServer = oSrvr.Name

    xFer.DestinationDatabase = dbVar.Name

    sCol = xFer.ScriptTransfer()

    For Each sLn In sCol

    sWriter.WriteLine(sLn)

    Next

    sWriter.Flush()

    sWriter.Close()

    Dts.TaskResult = Dts.Results.Success

    'Dts.TaskResult = Dts.Results.Failure

    End Sub

    End Class

    I have some connection manager objects in my package going to the server involved. Since this package was a work in progress and I haven't been back to it in a while, I don't recall if they are still needed or not. They might be there for setting up an FOR loop using SMO objects (as outlined above).

    Hope this helps.

    Beer's Law: Absolutum obsoletum
    "if it works it's out-of-date"

  • Hi,

    I have in my blog some scripts that could be useful about what you are doing.

    These scripts are made using SMO with PowerShell, which is very easy to install and use, my blog is in spanish, but is very straightforward to translate the useful words. 😉

    To script jobs, logins, backup devices and linked server use this link:

    http://practicascomunes.blogspot.com/2008/08/utilizando-poweshell-con-sql-2005-smo.html

    To script users, add them to roles, permissions in every db use this link:

    http://practicascomunes.blogspot.com/2008/08/mas-scripts-de-smo-sql-2005-y.html

    Hope this helps.

    Jose Santiago Oyervides.

Viewing 13 posts - 1 through 12 (of 12 total)

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