How does SMO make it easy to grab all code on a Microsoft SQL Server?

Question

There has recently been a lot of talk about putting database code in source control. I have read widely on this topic and there appears to be an unstated assumption that using SMO through PowerShell makes it very easy to do the following simultaneously:

1. Connect to a Microsoft SQL Server.
2. Grab every unit of code in a database (e.g. every table, index, stored procedure, function, view, permission, database config, ... but not data)
3. Save them in separate files, so that they're useful for source control.

Despite my best efforts, I can find no off-the-shelf SMO code that does this. It really is as if there is a secret SMO script that I cannot find. To me, this suggests that such a script is so easy to write that nobody has bothered publicising it.

dbatools has a handful of specialised functions for certain types of object (e.g. Get-DbaDbStoredProcedure), but finding every required dbatools function for this and correctly piping it to Export-DbaScript would be a big job. I'm also aware of some Redgate tools, but I'm specifically asking about SMO.

So, to put my question in an answerable form: What parts of the SMO make it very easy to write a script that simultaneously satisfies all of my three numbered points above?

Answer 1

You... can do it this way. I'm not aware of any pre-built tools to do this because it's one of the harder ways I've heard of to get a database into source control.

My method would be to use Visual Studio, create a database project, use schema compare to compare my production server to my source control and then apply those differences. This will create those files and folders for you, and then you can use the database project for your development. Putting it in source control is the normal steps.

This answer here may be helpful, https://stackoverflow.com/a/6705565/4008187

The key here is that when you do the schema compare that you make sure the source is your database and the target is your local solution, then just select which object(s) you want to sync (I would avoid logins, but you do you) and then apply them, this will create the required objects in visual studio. Then... you are done.

Going forward, make your changes in the visual studio project and then you can deploy from visual studio using the publish feature.

Answer 2

The Transfer class along with the ScriptingOptions class might be helpful. You'd have to investigate whether it has options to script to separate files.

However, how would you handle dependencies? You can't create table in wrong foreign key order, for instance. However you script it, if you store in separate files you would have this issue.

DACPAC files serves this purpose. They contains an XML doc that describes the database and then the dacpac engine reas this XML to create the object when you deploy it. I would consider using some dev tool that understands (can generate) DACPAC files from your db schema, and that also includes version control functionality.

Answer 3

Sql Server Schema Backup & Source Control.

This script generates all database object's schema (table, view, function, stored procedure, userdefine data types etc) of all databases in the Sql Server.

Sql Server Agent supports to run powershell scripts already. So you can define a scheduled job daily or weekly to backup only schema for source control to cover early versions of objects.

# Usage: 
# step 1: Open Powershell as an admin. 
# step 2: Go to the SqlServerSchemaBackup.ps1 script file existing folder.
# step 3: Type  .\SqlServerSchemaBackup.ps1


# Start Script
Set-ExecutionPolicy RemoteSigned

# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
#function SqlServerSchemaBackup([string]$serverName, [string]$dbname, [string]$scriptpath)
#{
$serverName="SqlServer"
$dbname="AdventureWorks2014" # optional for single db
$scriptpath="D:\Output"

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
  $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
  $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
  $SMOserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverName
  $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
  $databases = $srv.Databases
  $db = $srv.Databases[$dbname]
  $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
  #scriptr
  $deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
  $scr.Server = $srv
  $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
  $options.AllowSystemObjects = $false
  $options.IncludeDatabaseContext = $true
  $options.IncludeIfNotExists = $false
  $options.ClusteredIndexes = $true
  $options.Default = $true
  $options.DriAll = $true
  $options.Indexes = $true
  $options.NonClusteredIndexes = $true
  $options.IncludeHeaders = $false
  $options.ToFileOnly = $true
  $options.AppendToFile = $true
  $options.ScriptDrops = $false 

  # Set options for SMO.Scripter
  $scr.Options = $options
  
  $BaseSavePath = $scriptpath + $sql_server + "\"

  #Remove existing objects.
  Remove-Item $BaseSavePath -Recurse

 #Script server-level objects.
$ServerSavePath = $BaseSavePath
$ServerObjects = $SMOserver.BackupDevices
$ServerObjects += $SMOserver.Endpoints
$ServerObjects += $SMOserver.JobServer.Jobs
$ServerObjects += $SMOserver.LinkedServers
$ServerObjects += $SMOserver.Triggers

foreach ($ScriptThis in $ServerObjects | Where-Object { !($_.IsSystemObject) }) {
    #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
    $scriptr.Options.AppendToFile = $True
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False

    <#Script the Drop too#>
    $ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
    $ScriptDrop.Options.AppendToFile = $True
    $ScriptDrop.Options.AllowSystemObjects = $False
    $ScriptDrop.Options.ClusteredIndexes = $True
    $ScriptDrop.Options.DriAll = $True
    $ScriptDrop.Options.ScriptDrops = $True
    $ScriptDrop.Options.IncludeHeaders = $False
    $ScriptDrop.Options.ToFileOnly = $True
    $ScriptDrop.Options.Indexes = $True
    $ScriptDrop.Options.WithDependencies = $False

    <#This section builds folder structures.  Remove the date folder if you want to overwrite#>
    $TypeFolder = $ScriptThis.GetType().Name
    if ((Test-Path -Path "$ServerSavePath\$TypeFolder") -eq "true") `
    { "Scripting Out $TypeFolder $ScriptThis" } `
        else { new-item -type directory -name "$TypeFolder"-path "$ServerSavePath" }
    $ScriptFile = $ScriptThis -replace ":", "-" -replace "\\", "-"
    $ScriptDrop.Options.FileName = $ServerSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
    $scriptr.Options.FileName = $ServerSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"

    #This is where each object actually gets scripted one at a time.
    $ScriptDrop.Script($ScriptThis)
    $scriptr.Script($ScriptThis)
} #This ends the object scripting loop at the server level.

 #Script database-level objects.
foreach ($db in $databases) {
   # If ($db.Name -eq $dbname)   #Remove or comment this line to run the script for all dbs
   # { 
    $DatabaseObjects = $db.ApplicationRoles
    $DatabaseObjects += $db.Assemblies
    $DatabaseObjects += $db.ExtendedStoredProcedures
    $DatabaseObjects += $db.ExtendedProperties
    $DatabaseObjects += $db.PartitionFunctions
    $DatabaseObjects += $db.PartitionSchemes
    $DatabaseObjects += $db.Roles
    $DatabaseObjects += $db.Rules
    $DatabaseObjects += $db.Schemas
    $DatabaseObjects += $db.StoredProcedures
    $DatabaseObjects += $db.Synonyms
    $DatabaseObjects += $db.Tables
    $DatabaseObjects += $db.Triggers
    $DatabaseObjects += $db.UserDefinedAggregates
    $DatabaseObjects += $db.UserDefinedDataTypes
    $DatabaseObjects += $db.UserDefinedFunctions
    $DatabaseObjects += $db.UserDefinedTableTypes
    $DatabaseObjects += $db.UserDefinedTypes
    $DatabaseObjects += $db.Users
    $DatabaseObjects += $db.Views
#Build this portion of the directory structure out here.  Remove the existing directory and its contents first.
#$DatabaseSavePath = $BaseSavePath + "Databases\" + $db.Name
$DatabaseSavePath = $scriptpath + "\" + $db.Name


new-item -type directory -path "$DatabaseSavePath"

foreach ($ScriptThis in $DatabaseObjects | Where-Object { !($_.IsSystemObject) }) {
    #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)    
    $scriptr.Server = $srv
    $scriptr.Options.AppendToFile = $True
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False

    <#Script the Drop too#>
    $ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
    $ScriptDrop.Options.AppendToFile = $True
    $ScriptDrop.Options.AllowSystemObjects = $False
    $ScriptDrop.Options.ClusteredIndexes = $True
    $ScriptDrop.Options.DriAll = $True
    $ScriptDrop.Options.ScriptDrops = $True
    $ScriptDrop.Options.IncludeHeaders = $False
    $ScriptDrop.Options.ToFileOnly = $True
    $ScriptDrop.Options.Indexes = $True
    $ScriptDrop.Options.WithDependencies = $False

    <#This section builds folder structures.  Remove the date folder if you want to overwrite#>
    $TypeFolder = $ScriptThis.GetType().Name
    if ((Test-Path -Path "$DatabaseSavePath\$TypeFolder") -eq "true") `
    { "Scripting Out $TypeFolder $ScriptThis" } `
        else { new-item -type directory -name "$TypeFolder"-path "$DatabaseSavePath" }
    $ScriptFile = $ScriptThis -replace ":", "-" -replace "\\", "-"
    $ScriptDrop.Options.FileName = $DatabaseSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
    $scriptr.Options.FileName = $DatabaseSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"

    #This is where each object actually gets scripted one at a time.
    $ScriptDrop.Script($ScriptThis)
    $scriptr.Script($ScriptThis)

} #This ends the object scripting loop.
} #This ends the database loop.
#}

   

#}




#=============
# Execute
#=============
#ExportAllSchema SQL DB E:\Output
#ExportAllSchema $args[0] $args[1] $args[2]

Sql Server Schema Backup

Example

After my first answer I searched, modified, developed a new script for Sql Server Schema Backup.

Both of my answer is using SMO Scripting class. As I know this class is already used for other client applications such as SSMS - Generate Script task.

SMO Scripting class is dedicated for scripting of server & dbs.

SMO Transfer Class is dedicated to copy a db to another empty db. It could be modified to transfer into files but SMO Scripting class and Transfer class using with options and based on the same basic functions I think.

Both of scripts could easily be modified.

Github Sql Server Schema Backup

Answer 4

By using and modifying few source scripts I prepare the following script. It will generate separate folders for each object type and separate files for each object.

SQL Db Object - Type Folders

Table objects in Table folder

Export SQL Schema & Logins with Powershell Script

 # Usage:  powershell .\ExportSchemaSqlLogins.ps1 "SERVERNAME" "DATABASE" "C:\YourOutputPath\DATABASE"


# Start Script
Set-ExecutionPolicy RemoteSigned

# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function ExportSchema([string]$serverName, [string]$dbname, [string]$scriptpath)
{
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
  
  # 1.0 Sql Server Connection Part 
  $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
   
  $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")

  # 1.1 Sql Server Database Connection Part
  $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
  $db = $srv.Databases[$dbname]
  
  $deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"

  # 1.3 SMO Scripter part | object
  # What specific part of SMO makes this task easy?
  $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
  $scr.Server = $srv
  $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
  # What specific part of SMO makes this task easy?

  $options.AllowSystemObjects = $false
  $options.IncludeDatabaseContext = $true
  $options.IncludeIfNotExists = $false
  $options.ClusteredIndexes = $true
  $options.Default = $true
  $options.DriAll = $true
  $options.Indexes = $true
  $options.NonClusteredIndexes = $true
  $options.IncludeHeaders = $false
  $options.ToFileOnly = $true
  $options.AppendToFile = $true
  $options.ScriptDrops = $false 

  # Set options for SMO.Scripter
  $scr.Options = $options

  #=============
  # Tables
  #=============
  
  Foreach ($tb in $db.Tables)
  {
  $options.FileName = $scriptpath + "\Tables\$($tb).sql"
  New-Item $options.FileName -type file -force | Out-Null
  
   If ($tb.IsSystemObject -eq $FALSE)
   {
    $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
    $smoObjects.Add($tb.Urn)
    $scr.Script($smoObjects)
   }
  }

  #=============
  # Views
  #=============
  
  $views = $db.Views | where {$_.IsSystemObject -eq $false}
  Foreach ($view in $views)
  {
  $options.FileName = $scriptpath + "\Views\$($view).sql"
  New-Item $options.FileName -type file -force | Out-Null

    if ($views -ne $null)
    {
     $scr.Script($view)
   }
  }

  #=============
  # StoredProcedures
  #=============
  $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
  
  Foreach ($StoredProcedure in $StoredProcedures)
  {
  $options.FileName = $scriptpath + "\StoredProcedures\$($StoredProcedure).sql"
  New-Item $options.FileName -type file -force | Out-Null
    if ($StoredProcedures -ne $null)
    {   
     $scr.Script($StoredProcedure)
   }
  } 

  #=============
  # Functions
  #=============
  $UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
  
  Foreach ($function in $UserDefinedFunctions)
  {
  $options.FileName = $scriptpath + "\Functions\$($function).sql"
  New-Item $options.FileName -type file -force | Out-Null

    if ($UserDefinedFunctions -ne $null)
    {
     $scr.Script($function)
   }
  } 

  #=============
  # DBTriggers
  #=============
  $DBTriggers = $db.Triggers
  
  foreach ($trigger in $db.triggers)
  {
  $options.FileName = $scriptpath + "\Db_Triggers\$($trigger).sql"
  New-Item $options.FileName -type file -force | Out-Null
    if ($DBTriggers -ne $null)
    {
      $scr.Script($DBTriggers)
    }
  }

  #=============
  # Table Triggers
  #=============
 
  Foreach ($tb in $db.Tables)
  {       
    if($tb.triggers -ne $null)
    {
      foreach ($trigger in $tb.triggers)
      {
      $options.FileName = $scriptpath + "\Table_Triggers\$($tb)_$($trigger).sql"
      New-Item $options.FileName -type file -force | Out-Null
        $scr.Script($trigger)
      }
    }
  } 

   

}




#$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
#$conn.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASE;User ID=sqlusername;Password=sqluserpass;MultipleActiveResultSets=True;Application Name=Powershell"

#ExportSchema "SERVERNAME" "DATABASE" "E:\Script_Files\DATABASE"


function ExportSQLLogins{
    [cmdletbinding()]
    param([parameter(Mandatory=$true)][string] $source
            ,[string] $ApplyTo
            ,[string[]] $logins
            ,[string] $outputpath) #=([Environment]::GetFolderPath("MyDocuments"           ))
#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
 
#create initial SMO object
$smosource = new-object ('Microsoft.SqlServer.Management.Smo.Server') $source
 
#Make sure we script out the SID
$so = new-object microsoft.sqlserver.management.smo.scriptingoptions
$so.LoginSid = $true
 
#set output filename
#$filename = $source.Replace('/','_') + '_' + (Get-Date -Format 'yyyyMMddHHmm') + '_logins.sql'
$filename = $source + '_logins.sql'
$outfile = Join-Path -Path $outputpath -ChildPath $filename
 
#If no logins explicitly declared, assume all non-system logins
if(!($logins)){
    $logins = ($smosource.Logins | Where-Object {$_.IsSystemObject -eq $false}).Name.Trim()
}
 
foreach($loginname in $logins){
    #get login object
    $login = $smosource.Logins[$loginname]
 
    #Script out the login, remove the "DISABLE" statement included by the .Script() method
    $lscript = $login.Script($so) | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
    $lscript = $lscript -join ' '
 
    #If SQL Login, sort password, insert into script
    if($login.LoginType -eq 'SqlLogin'){
 
      $sql = "SELECT convert(varbinary(256),password_hash) as hashedpass FROM sys.sql_logins where name='"+$loginname+"'"
      $hashedpass = ($smosource.databases['tempdb'].ExecuteWithResults($sql)).Tables.hashedpass
      $passtring = ConvertTo-SQLHashString $hashedpass
      $rndpw = $lscript.Substring($lscript.IndexOf('PASSWORD'),$lscript.IndexOf(', SID')-$lscript.IndexOf('PASSWORD'))
 
      $comment = $lscript.Substring($lscript.IndexOf('/*'),$lscript.IndexOf('*/')-$lscript.IndexOf('/*')+2)
      $lscript = $lscript.Replace($comment,'')
      $lscript = $lscript.Replace($rndpw,"PASSWORD = $passtring HASHED")
    }
 
    #script login to out file
    $lscript | Out-File -Append -FilePath $outfile
 
    #if ApplyTo is specified, execute the login creation on the ApplyTo instance
    If($ApplyTo){
        $smotarget = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ApplyTo
 
        if(!($smotarget.logins.name -contains $loginname)){
            $smotarget.Databases['tempdb'].ExecuteNonQuery($lscript)
            $outmsg='Login ' + $login.name + ' created.'
            }
        else{
            $outmsg='Login ' + $login.name + ' skipped, already exists on target.'
            }
        Write-Verbose $outmsg
        }
    }
}

function ConvertTo-SQLHashString{
  param([parameter(Mandatory=$true)] $binhash)
 
  $outstring = '0x'
  $binhash | ForEach-Object {$outstring += ('{0:X}' -f $_).PadLeft(2, '0')}
 
  return $outstring
}

#Create a SQL Script in My Documents for all logins
#Export-SQLLogins -source 'SQL'


#=============
# Execute
#=============

ExportSchema $args[0] $args[1] $args[2]
ExportSQLLogins -source $args[0] -outputpath $args[2]

Usage

1.Copy the script and save as an ExportSchemaSqlLogins.ps1 file.

2.Open PowerShell as an admin

3.Goto the script folder.

4.Run as following.

.\ExportSchemaSqlLogins.ps1 "SERVERNAME" "DBNAME" "E:\Script_Files\DBNAME"

I'm publishing the script on my Github