August 26, 2008 at 5:23 pm
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.
August 27, 2008 at 12:34 am
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
August 27, 2008 at 12:32 pm
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
August 28, 2008 at 1:49 am
Hi,
EXEC dbo.SP_GENERATE_TABLE_SCRIPT 'TESTDB','C\TempTest'
I ran this but did not get anything in 'C\TempTest'
August 28, 2008 at 4:42 am
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
August 28, 2008 at 5:08 am
Can you give me the bit detail how did you added scripts into vss,i have vss and mssql 2005 also.
Thanks
Raju
August 28, 2008 at 5:19 am
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'
August 28, 2008 at 9:08 am
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\'
August 28, 2008 at 7:47 pm
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 ;).
August 29, 2008 at 9:30 am
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
September 2, 2008 at 4:08 am
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
September 3, 2008 at 8:46 am
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"
September 4, 2008 at 9:16 am
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