Generate SQLServer Scripts with PowerShell

I needed a new script to generate SQL scripts for tables, views etc. The old script did export all tables as files with extension "TAB", all views with extension "VIW", etc. I wanted to keep this, but needed all the files in correct order for dependencies. So here we go: my first PowerShell script.

In order to connect to a SQLServer we must first load some assemblies:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null

Now it is possible to connect to the server:

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" "NAME_OF_SERVER"
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Login = "USER_NAME"
$srv.ConnectionContext.Password ="PASSWORD"

Get a reference to the database:

$dbName = "DATABASE_NAME"
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbName]

Get a scripter instance and set options:

$scripter = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$scripter.Server = $srv
$scrOpts = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$scrOpts.Encoding = [System.Text.Encoding]::GetEncoding(1252)
$scrOpts.AllowSystemObjects = $false
$scrOpts.AnsiFile = $true
$scrOpts.AppendToFile = $false
$scrOpts.ClusteredIndexes = $true
$scrOpts.Default = $true
$scrOpts.DriAll = $true
$scrOpts.Indexes = $true
$scrOpts.NonClusteredIndexes = $true
$scrOpts.ToFileOnly = $true
$scrOpts.WithDependencies = $false
$scrOpts.Triggers = $true
$scripter.Options = $scrOpts

See MSDN for all Options .

Export all tables:

# Read all tables into an array, filter out system tables
$Objects = @()
$Objects += $db.Tables | where {$_.IsSystemObject -eq $false}
# Find dependencies
$dependencyTree = $scripter.DiscoverDependencies($Objects, $true)
# Create a list of all needed objects in correct order
$depCollection = $scripter.WalkDependencies($dependencyTree);

Unfortunatelly this did nor work the first time, because there was a trigger on a table which called a stored procedure. DiscoverDependencies also returned this procedure and all views and other objects which this procedure was dependent of. Filtering out the procedure helped. To get this done one can set a callback function which gets called by DiscoverDependencies:

function ScriptingFilter([Microsoft.SqlServer.Management.Sdk.Sfc.Urn]$urn) {
    $item = $srv.GetSmoObject($urn)
    if($item -is [Microsoft.SqlServer.Management.Smo.StoredProcedure])
    {
        Write-Host "  Filter: " $item " => " $type
        return $true
    } else {
        return $false
    }
}

$scripter.FilterCallbackFunction = get-content Function:\ScriptingFilter

Now create a script file for each object:

# Create a UrnCollection of all objects
$urns = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
foreach($dep in $depCollection) { $urns.add($dep.Urn) }

$onlyOne = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
$i = 0
$max = $urnCollection.Count

foreach($urn in $urnCollection)
{
    $onlyOne.clear()
    $item = $srv.GetSmoObject($urn)     $ext = "SQL"
    if($item -is [Microsoft.SqlServer.Management.Smo.Table])
    {
        $ext = "TAB"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.View])
    {
        $ext = "VIW"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.StoredProcedure])
    {
        $ext = "PRC"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.UserDefinedFunction])
    {
        $ext = "UDF"
    }
    if($item -is [Microsoft.SqlServer.Management.Smo.UserDefinedDataType])
    {
        $ext = "UDT"
    }


    # Filter out some more objects we do not need     if($item.Name.StartsWith("spt_") -or $item.IsSystemObject -eq $true)
    {
        Write-Host " Ignored: " $item.Name
     } else {
            $count = $count + 1
            $scripter.Options.FileName = $scriptPath + $count.ToString("0000") + '.' + $item.Schema + "." + $item.Name + "." + $ext
            $scripter.Script($onlyOne)
            $i = $i + 1
    }
}

Finally I added some status information using Write-Progress commandlet and created some functions so that it was possible to script first all tables, then all views and procedures.

Some helpfull links:

• http://sqlblog.com/blogs/ben_miller/archive/2007/10/18/scripting-tables-views-and-data-using-smo-part-3.aspx
• http://www.yaldex.com/sql_server_tutorial_3/ch11lev1sec4.html
• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scripter.aspx

The full script:

#Set-ExecutionPolicy RemoteSigned

#
# Extension fuer ein bestimmtes SmoObject ermitteln
#
function GetTypeExt([Microsoft.SqlServer.Management.Smo.SqlSmoObject] $Item)
{
    $ext = "SQL"
    if($Item -is [Microsoft.SqlServer.Management.Smo.Table])
    {
        $ext = "TAB"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.View])
    {
        $ext = "VIW"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.StoredProcedure])
    {
        $ext = "PRC"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.UserDefinedFunction])
    {
        $ext = "UDF"
    }
    if($Item -is [Microsoft.SqlServer.Management.Smo.UserDefinedDataType])
    {
        $ext = "UDT"
    }
    return $ext
}

#
# Function um bestimmte Elemente aus den Abhaengigkeiten auszuschliessen
#
function ScriptingFilter([Microsoft.SqlServer.Management.Sdk.Sfc.Urn]$urn) {
    $item = $srv.GetSmoObject($urn)
    $type = GetTypeExt($item)
    if(-not $Filter -eq "" -and $type.StartsWith($Filter))
    {
        Write-Host "  Filter: " $item " => " $type
        return $true
    } else {
        return $false
    }
}

#
# Generiert Scripts ohne Abhaengigkeiten fuer Objekte in $Objects
#
function GenScripts()
{
    $urns = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
    foreach($dep in $Objects) { $urns.add($dep.Urn) }
    GenScriptsForObjects $urns
}

#
# Generiert Scripts mit Abhaengigkeiten fuer Objekte in $Objects
#
function GenScriptsWithDependencies()
{
    $scripter.FilterCallbackFunction = get-content Function:\ScriptingFilter
    if($Objects.Length -ne 0)
    {
        $dependencyTree = $scripter.DiscoverDependencies($Objects, $true)
        $depCollection = $scripter.WalkDependencies($dependencyTree);
        $urns = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
        foreach($dep in $depCollection) { $urns.add($dep.Urn) }
        GenScriptsForObjects $urns
    }
}

#
# Generiert Scripts fuer eine Collection von SmoObjects
#
function GenScriptsForObjects([Microsoft.SqlServer.Management.Smo.UrnCollection]$urnCollection)
{
        $onlyOne = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
        $i = 0
        $max = $urnCollection.Count

        foreach($urn in $urnCollection)
        {
            $onlyOne.clear()
            $item = $srv.GetSmoObject($urn)
           
            Write-Progress -Activity "Script objects..." -status $item.Name -percentComplete ($i / $max * 100)
           
            $ext = GetTypeExt($item)
           
            if($item.Name.StartsWith("spt_") -or $item.IsSystemObject -eq $true)
            {
                Write-Host "  Ignored: " $item.Name
            } else {
                if(-not $UrnsDone.Contains($item.Urn))
                {
                    $onlyOne.add($item.Urn)
                    $global:count = $UrnsDone.Count + 1
                    $scripter.Options.FileName = $scriptPath + $count.ToString("0000") + '.' + $item.Schema + "." + $item.Name + "." + $ext
                    $scripter.Script($onlyOne)
                    $UrnsDone.add($item.Urn)
                    $i = $i + 1
                }
            }
        }
}

#
# Hauptfunktion: Stellt Verbindung mit SQLServer her, ermittelt die Objekte und Scripted diese
#
function GenerateDBScript([string]$dbServer, [string]$dbName, [string]$dbUser, [string]$dbPsw, [string]$scriptPath)
{
    if ($scriptPath[-1] -ne "\")
    {
        $scriptPath = $scriptPath + "\"
    }
    if (!(Test-Path -path $scriptPath))
    {
       New-Item $scriptPath -type directory
    }
    if (Test-Path -path ($scriptPath + "0*.*"))
    {
        $msg = "Verzeichnis """ + $scriptPath + """ enthält bereits Script-Dateien. Zuerst alle Dateien löschen."
        [System.Windows.Forms.MessageBox]::Show($msg) | out-null
        $msg
        explorer $scriptPath
    } else {
        "Generate scripts for server: " + $dbServer + " database: " + $dbName
        "  to Path: " + $scriptPath

        [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" $dbServer
        $srv.ConnectionContext.LoginSecure = $false
        $srv.ConnectionContext.Login = $dbUser
        $srv.ConnectionContext.Password = $dbPsw
        $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")

        $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
        $db = $srv.Databases[$dbName]

        $scripter = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
        $scripter.Server = $srv

        $scrOpts = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
        #$scrOpts.Encoding = [System.Text.Encoding]::GetEncoding(1252)
        $scrOpts.AllowSystemObjects = $false
        $scrOpts.AnsiFile = $true
        $scrOpts.AppendToFile = $false
        $scrOpts.ClusteredIndexes = $true
        $scrOpts.Default = $true
        $scrOpts.DriAll = $true
        $scrOpts.Indexes = $true
        $scrOpts.NonClusteredIndexes = $true
        $scrOpts.ToFileOnly = $true
        $scrOpts.WithDependencies = $false
        $scrOpts.Triggers = $true
        $scripter.Options = $scrOpts

        $UrnsDone = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection;
        $Filter = ""
        $global:count = 0

        try {

            $Objects = @()
           
            Write-Progress -Activity "Script Objects..." -status "User Defined Data Types" -percentComplete 0
            $Res = $db.UserDefinedDataTypes | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }
       
            Write-Progress -Activity "Script Objects..." -status "Tables" -percentComplete 0
            $Res = $db.Tables | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            $Filter = "PRC"
            GenScriptsWithDependencies
           
           
            $Objects = @()
       
            Write-Progress -Activity "Script Objects..." -status "User Defined Functions" -percentComplete 0
            $Res = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            Write-Progress -Activity "Script Objects..." -status "Views" -percentComplete 0
            $Res = $db.Views | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            $Filter = "PRC"
            GenScriptsWithDependencies

           
            $Objects = @()
           
            Write-Progress -Activity "Script Objects..." -status "DB Triggers" -percentComplete 0
            $Res = $db.Triggers | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            Write-Progress -Activity "Script Objects..." -status "Procedures" -percentComplete 0
            $Res = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
            if($Res)
            {
                $Objects += $Res
            }

            $Filter = ""
            GenScriptsWithDependencies

        } catch {
            "EXCEPTION: " + $_.Exception.GetBaseException().Message
            throw $_.Exception
        }

        "Done. " + $count + " Dateien erstellt in " + $scriptPath
        explorer $scriptPath
    }
}

# GenerateDBScript $args[0] $args[1] $args[2] $args[3] $args[4]
GenerateDBScript "SERVER" "DATABASE" "USER" "PASSWORD" "DIRECTORY"