December 21, 2007 at 1:51 pm
I am trying to use DMO to exactly reproduce the output that I get from
Microsoft SQL Server Enterprise manager when I choose a database and
then "Generate SQL Scripts" and choose the following settings:
General:
Script All Objects
Formatting:
Generate the CREATE command for each object
Generate the DROP command for each object
Generate scripts for all dependent objects
Include extended properties
Options:
Script database
Script database users and database roles
Script SQL Server Logins (Windows and SQL Server logins)
Script object-level permissions
Script indexes
Script full-text indexes
Script PRIMARY keys, FOREIGN keys, defaults, and check constraints
Windows text (ANSI)
Create one file per object
However, I do not want it to be exactly like that, as some things I do not want to
delete first, but rather check if they exist first then only add them if they do.
Specifically:
Defaults,Users, Roles, and Logins
It turns out that when I tried to do this, generating the scripts for views, would for
some reason trigger making several other objects at the same time regardless of your
settings. So it required being made differtly all by itself.
I am posting this code here, because basically I have yet to see it all in one place. So in
the spirit of the season, and just because I am an all around nice guy. :) Here is the code
in VBScript. It is ugly and not commented at all... but I think you can get the point...
Replace the values in sServer and sDatabase with stuff that works for you.
sub Main
Dim sServer
Dim sDatabase
sServer = "(local)"
sDatabase= "pubs"
'Script
ScriptDefaultsUsersRolesAndLogins sServer, sDatabase
ScriptEverythingElse sServer, sDatabase
ScriptViews sServer, sDatabase
MsgBox "Done!"
end sub
Sub Script(sServer, sDatabase)
Dim oSS
Dim oDb
Dim oT
Dim ScriptType1
Dim ScriptType2
ScriptType1 = (73736 OR 262144 OR 1 OR 4 OR 128 )
ScriptType2 = (4194304 OR 8192)
Set oSS = CreateObject("SQLDMO.SQLServer2")
Set oT = CreateObject("SQLDMO.Transfer2")
with oSS
.LoginSecure = True
.Connect sServer
end with
set oDb=oSS.Databases(sDatabase)
with oT
.CopyAllDefaults = True
.CopyAllObjects = True
.CopyAllRules = True
.CopyAllStoredProcedures = True
.CopyAllTables = True
.CopyAllTriggers = True
.CopyAllUserDefinedDatatypes = True
.CopyAllViews = True
.CopyData = 0
.CopySchema = True
.CopyAllFunctions = True
.IncludeDependencies = True
.IncludeLogins = True
.IncludeUsers = True
.IncludeDB = True
.DropDestObjectsFirst = True
.ScriptType = ScriptType1
.Script2Type = ScriptType2
.SourceTranslateChar = False
.UseCollation = False
.UseDestTransaction = False
end with
oDb.ScriptTransfer oT, 4, "C:\" & sDatabase
End Sub
Sub ScriptEverythingElse(sServer, sDatabase)
Dim oSS
Dim oDb
Dim oT
Dim ScriptType1
Dim ScriptType2
ScriptType1 = (73736 OR 262144 OR 1 OR 4 OR 128 )
ScriptType2 = (4194304 OR 8192)
Set oSS = CreateObject("SQLDMO.SQLServer2")
Set oT = CreateObject("SQLDMO.Transfer2")
with oSS
.LoginSecure = True
.Connect sServer
end with
set oDb=oSS.Databases(sDatabase)
with oT
.CopyAllDefaults = False
.CopyAllObjects = False
.CopyAllRules = True
.CopyAllStoredProcedures = True
.CopyAllTables = True
.CopyAllTriggers = True
.CopyAllUserDefinedDatatypes = True
.CopyAllViews = False
.CopyData = 0
.CopySchema = True
.CopyAllFunctions = True
.IncludeDependencies = False
.IncludeLogins = False
.IncludeUsers = False
.IncludeDB = False
.DropDestObjectsFirst = True
.ScriptType = ScriptType1
.Script2Type = ScriptType2
.SourceTranslateChar = False
.UseCollation = False
.UseDestTransaction = False
end with
oDb.ScriptTransfer oT, 4, "C:\" & sDatabase
End Sub
Sub ScriptDefaultsUsersRolesAndLogins(sServer, sDatabase)
Dim oSS
Dim oDb
Dim oT
Dim ScriptType1
Dim ScriptType2
ScriptType1 = (73736 OR 262144 OR 4 OR 128 OR 4096)
ScriptType2 = (4194304 OR 8192)
Set oSS = CreateObject("SQLDMO.SQLServer2")
Set oT = CreateObject("SQLDMO.Transfer2")
with oSS
.LoginSecure = True
.Connect sServer
end with
set oDb=oSS.Databases(sDatabase)
with oT
.CopyAllDefaults = True
.CopyAllObjects = False
.CopyAllRules = False
.CopyAllStoredProcedures = False
.CopyAllTables = False
.CopyAllTriggers = False
.CopyAllUserDefinedDatatypes = False
.CopyAllViews = False
.CopyData = 0
.CopySchema = False
.CopyAllFunctions = False
.IncludeDependencies = False
.IncludeLogins = True
.IncludeUsers = True
.IncludeDB = False
.DropDestObjectsFirst = False
.ScriptType = ScriptType1
.Script2Type = ScriptType2
.SourceTranslateChar = False
.UseCollation = False
.UseDestTransaction = False
end with
oDb.ScriptTransfer oT, 4, "C:\" & sDatabase
End Sub
Sub ScriptViews(sServer, sDatabase)
Dim oSS
Dim oDb
Dim ScriptType1
Dim ScriptType2
ScriptType1 = (73736 OR 262144 OR 1 OR 4 OR 128 )
ScriptType2 = (4194304 OR 8192)
Set oSS = CreateObject("SQLDMO.SQLServer2")
with oSS
.LoginSecure = True
.Connect sServer
end with
set oDb=oSS.Databases(sDatabase)
For each item in oDb.Views
If item.SystemObject = False Then
sFileName = "C:\" & sDatabase & "\" & item.Owner & "." & Replace(item.Name, "\", "-") & ".VIW"
item.Script ScriptType1, sFileName, ScriptType2
End If
Next
End Sub
December 21, 2007 at 4:56 pm
Thanks and feel free to submit this as a script. We can get it out in the newsletter then.
December 21, 2007 at 10:59 pm
Not sure I follow you... please use it, I cobbled it together from about 7 different sources and then tweaked it a bit using the wonderful microsoft documentation 🙂 How would you have me to submit it? And what do I win if you use it??? 🙂 I will go see if I can post it to the Scripts area.
December 21, 2007 at 11:08 pm
Well I gave it a shot. But apparently the script entry screen does not handle preformatted text so well. No matter, i am sure people can come to the forum and find it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply