Press "Enter" to skip to content

Slack Asks: Scripting out all objects from a SQL Server database with PowerShell and dbatools

Last updated on November 9, 2017

@brianszadek says: I am looking to automate the scripting of objects with PowerShell for easier source control integration.

I thought this would be a little bit easier, but it looks like the SMO way of doing this is still fairly complicated. Maybe something I can look at simplifying for the project in a bit ๐Ÿ™‚

For completeness I also included my favorite tool (schemazen) and Microsoft’s new scripting tool if you want a quick shell approach.

PowerShell: (dbatools)

$SqlInstance = "192.168.0.23,1433"
$DatabaseName = "breakme"
$Server = Connect-DbaInstance $SqlInstance
$Database = $Server.Databases[$DatabaseName]

# Define an empty list
$ObjectList = @()
$Options = New-DbaScriptingOption
$Options.AllowSystemObjects =
  $Options.AppendToFile = $false

$Options.Triggers =
  $Options.ToFileOnly =
  $Options.ScriptBatchTerminator =
  $Options.DriAll =
  $Options.Indexes =
  $Options.Default =
  $Options.SchemaQualify =
  $Options.ClusteredIndexes =
  $Options.NonClusteredIndexes = $true

# Surprisingly, $Database.EnumObjects will return a set of strings, not SMO objects, this is kind of sucky.
$ObjectList += foreach ($ob in $Database.EnumObjects()){ # Get all objects
  if ($ob.Schema -notin "sys","INFORMATION_SCHEMA"){ # Not in system schemas
    $o = $Database.Resolve($ob.Urn) # Resolve the object path to an SMO object
    if (($o | Get-Member | Select-Object -exp Name) -contains "Script"){ # If it has a script property
      $o # Return it
    }
  }
}


function Rename-ObjectToSqlFile {
  param ($ObjectName)
  $Name =  $ObjectName -match '.*\/(.*)'
  if ($Name -eq $false){
    $Name = $ObjectName
  } else {
    $Name = $matches[1]
  }
  $Name = $Name + ".sql"
  return $Name
}

# Iterate, get one file per object, decide on how you want to name them, because things like event notifications are uris
foreach ($obj in $ObjectList) {
  $Name = Rename-ObjectToSqlFile -ObjectName $obj.name
  Export-DbaScript $obj -ScriptingOptionsObject $Options -Path $Name # Export it with the dbatools powers
}
# Or export based on category
Export-DbaScript $ObjectList -ScriptingOptionsObject $Options # Export object categories to each file

Note: Export-DbaScript currently has a bug that prevents servers using SQL Authentication from scripting, this has been fixed in the next version.

Command line executables

Schema Zenย (C#) (Free, OSS)

If you haven’t heard of SchemaZen, its a great and insanely fast tool for exporting all the objects in a database to a folder, and if needed, take the same folder and create a database from it.

There are also some experimental data export features.

MSSQL Scripter (python) (Free, OSS)

If you haven’t used the MSSSQL Scripter, its Microsoft’s tool that utilizes the newย SqlToolsService to request and script out DDL information. They have a lovely architecture page if you want to learn more.

4 Comments

  1. Vladimir Sotirov Vladimir Sotirov

    You should add a parameter for the root folder to export the scripts to.

    • ck ck

      That’s a great idea!
      I was also just thinking on this a minute ago, and I should probably just make a subfolder for each script type (like schemazen already does.)

      If I remember I will take a crack at it later today ๐Ÿ˜€

      • rich rich

        Hi,
        did you ever create a script that does different object types in sub folders?

        thanks
        rich

        • ck ck

          Hey Rich – I hadn’t – but the simplest way would probably be by inspecting the $obj.urn.type and adding that to the -Path parameter, it will list out things like ServiceRoute, SqlAssembly, StoredProcedure, etc.

Leave a Reply to Vladimir Sotirov Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.