Generate SQL Scripts from Database

,

Modify the script to have your server in sServer and your database in sDatabase. By default the output will be in a directory on the C:\<databasename> . This should also be changed but is left as an exercise for the reader.

I attempted 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 differently 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

Rate

Share

Share

Rate