Script SQL Server database to individual files for schema history

SMOScripter.ps1

param ([string]$s, [string[]] $d, [switch]$commit, [switch]$verbose, [string]$path=(Get-Location))

# Install-Module -Name SqlServer -Scope CurrentUser

# Systemd service unit:
#   ExecStart=/usr/bin/pwsh /localdata/SMOSchema/scripter.ps1 -s ALL -commit
#
# Add to $SERVICENAME.service.d/override.conf 
#   [Service]
#   Environment=SQLCMDUSER=
#   Environment=SQLCMDPASS=


$ErrorActionPreference = "Stop"
$DebugPreference = "Continue"

Start-Transcript -Append -Path "$(Get-Location)\scripter-$(Get-Date -Format 'yyyyMM').log"

Import-Module SqlServer

$global:ymd = Get-Date -Format "yyyyMMdd"


function normalize ($txt) {
    # Replace non-alphanum characters in filename string
    return "$txt" -replace '[\[\]]','' -replace '[^A-Za-z0-9_\.]','_'
}


function linends ($file) {
    # SMO CommandTerminator not respecting Unix style line endings
    # Rewrite file with LF line endings

    $text = [IO.File]::ReadAllText($file) -replace "`r`n", "`n"
    [IO.File]::WriteAllText($file, $text)
}


function scriptDb ($srv, $dbs) {

    if ($IsLinux) {
        # FakeDNS and credentials when not on Windows
        $ip = switch($srv) {
            'SERVER1' { '192.168.100.1' }
            'SERVER2' { '192.168.100.2' } 
        }
        Write-Debug "$srv -> $ip"

        $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ip

        $context = $server.ConnectionContext
        $context.LoginSecure = $false
        $context.Login = $Env:SQLCMDUSER
        $context.Password = $Env:SQLCMDPASS
    } else {
        $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srv
    }

    #$server.SetDefaultInitFields($true) 
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Table], $True)
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.View], $True)
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.UserDefinedFunction], $True)
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Trigger], $True)
    $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.UserDefinedTableType], $True)

    #$server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DataFile], $false)  # Has no defaults
    #$server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Sequence], $True)  # Error: unknown property Length

    $so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

    $so.ContinueScriptingOnError = $false

    $so.ExtendedProperties  = $false
    $so.ScriptData   = $false
    $so.ScriptSchema = $true

    $so.AllowSystemObjects  = $false
    $so.AnsiFile            = $true
    $so.DriAllConstraints   = $true
    $so.IncludeHeaders      = $false
    $so.NoCollation         = $true
    $so.NoCommandTerminator = $false
    $so.Permissions         = $true
    $so.SchemaQualify       = $true
    $so.SchemaQualifyForeignKeysReferences = $true
    $so.ScriptBatchTerminator = $true
    $so.ScriptDrops         = $false
    $so.ToFileOnly          = $true

    $so.ClusteredIndexes    = $true
    #$so.ColumnStoreIndexes  = $true
    $so.DriClustered        = $true
    $so.DriIndexes          = $true
    $so.DriNonClustered     = $true
    $so.FullTextIndexes     = $true
    $so.Indexes             = $true
    $so.NonClusteredIndexes = $true
    #$so.Triggers            = $true

    $so.AppendToFile = $false
    $so.EnforceScriptingOptions = $true
    $so.Encoding = [System.Text.Encoding]::UTF8

    $srvName = "$server".replace("[", "").replace("]", "")

    # Script all databases if list is not specified
    if ($dbs -eq $null) {
        $dbs = $server.Databases
    }

    $oc = 0
    foreach ($db in $dbs) {

        if ($db.GetType().Name -eq "String") {
            # Get database object when $db is a string name
            $db = $server.Databases[$db]
        }

        if (!$db.IsSystemObject) {
            $dbName = "$db".replace("[", "").replace("]", "")

            Write-Host "$srvName $dbName"

            $srvPath = Join-Path "$path" "$srv"
            $dbPath = Join-Path "$srvPath" "$dbName"

            if ( !(Test-Path $dbPath)) { $null = new-item -type directory -name "$dbName" -path "$srvPath" }

            # Delete objects from previous commit to detect drops
            Remove-Item "$dbPath/*" -Recurse
 
            foreach ($Type in $IncludeTypes) {
                $typePath = Join-Path "$dbPath" "$Type"
                if ( !(Test-Path $typePath)) { $null = new-item -type directory -name "$Type" -path "$dbPath" }

                $c = 0
                # Iterate over objects, excluding those with GUIDs in names assumed to be system generated
                foreach ($obj in $db.$Type | where { $ExcludeSchemas -notcontains $_.Schema -and $_.Name -notmatch '[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}'}) {
                    $schema, $objName = $(normalize "$obj").split('.')
                    if ($objName -eq $null) { 
                        $objName = $schema 
                        $schema = "_"
                    }

                    $schemaPath = Join-Path "$typePath" "$schema"
                    if ( !(Test-Path $schemaPath)) { $null = new-item -type directory -name "$schema" -path "$typePath" }

                    $outFile = $(Join-Path "$schemaPath" "$objName") + ".sql" 
                    try {
                        $so.Filename = $outFile
                        $obj.Script($so)
                        #linends $outFile

                        if ($verbose) { Write-Host "-- $outFile" }
                        $c++
                    }
                    catch {
                        Throw $_
                    }
                }
                Write-Host "- $Type $c"
                $oc += $c
            }

            # Table triggers are elements of tables, root Triggers collection are database triggers
            $Type = "Triggers"
            $typePath = Join-Path "$dbPath" "$Type"
            if ( !(Test-Path $typePath)) { $null = new-item -type directory -name "$Type" -path "$dbPath" }

            $c = 0
            foreach ($trg in $db.Tables | where { $ExcludeSchemas -notcontains $_.Schema } | % { $_.Triggers }) {
                # Skip system generated triggers (GUID in name)
                if ( $trg.Name -match '[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}' ) { Continue }
                
                $schema = $(normalize $trg.Parent.schema)

                $trgPath = Join-path "$typePath" "$schema"
                if ( !(Test-Path $trgPath)) { $null = new-item -type directory -name "$schema" -path "$typePath" }

                $objName = $(normalize $trg.Parent.Name) + "." + $(normalize "$trg")
                $outFile = $(Join-Path "$trgPath" "$objName") + ".sql"

                try {
                    $so.Filename = $outFile
                    $trg.Script($so)
                    #linends $outFile

                    if ($verbose) { Write-Host "-- $outFile" }
                    $c++
                }
                catch {
                    Throw $_
                    return 0
                }
            }
            Write-Host "- TableTriggers $c"
            $oc += $c
        }
    }
    return $oc
}


echo "START $(Get-Date) | s:$s d:$d commit:$commit path:$path ymd:$ymd"

$jobtimer = [System.Diagnostics.Stopwatch]::StartNew()

$ExcludeSchemas = @("sys", "Information_Schema", "cdc")
$IncludeTypes = @(
    "Tables", 
    "Views", 
    "StoredProcedures", 
    "UserDefinedFunctions", 
    "Sequences", 
    "Triggers",
    "UserDefinedTableTypes"
)

$toc = 0

try {
    if ( $s -eq "ALL") {
        $servers = "SERVER1", "SERVER2"
        foreach ($server in $servers) {
            $oc = scriptDb $server
            $toc += $oc
            Write-Host "SOURCE $server $(Get-Date) : $oc objects"
        }
    } else {
        Write-Host "Server $s"
        $toc = scriptDb $s $d
    }

    $ver = Join-Path "$(Get-Location)" "VERSION.txt"
    echo "$ymd" | Out-File -Encoding "ASCII" -FilePath $ver
    #linends $ver

    if ( $commit -eq $true ) {
        Write-Host "COMMIT $ymd"
        git add "$path/*"
        git commit -am "$ymd"
        git push
    }
}
finally {
    Write-Host "END $(Get-Date) | $toc objects $($jobtimer.Elapsed.Minutes) mins"
    Stop-Transcript
}