Generate SQL Scripts from Database

Absinthe-428914, 2011-12-16 (first published: 2007-12-27)

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