Script SQL Server Objects with PowerShell

Standard

export_boxScripting SQL Server objects manually through the SQL Server Management Studio can be a tedious task. Last week I published a script to export database objects with PowerShell. I wanted to take this a little further and create a solution to export SQL Server objects as well.

Because this would be a nice addition to the PSSQLLib module, this function is also included in the library from today.

How it works

The function works by selecting all the different server objects needed and loops through the list to export to a specific directory of the type of object.

The script will export the following types of objects:

  • Jobs
  • Linked Servers
  • Logins
  • Server Roles
  • Triggers
  • Mail

You can exclude objects by specifying a include parameters. For instance, if you want to exclude jobs:

Export-SQLServerObject -inst 'yourinstance' -path 'yourpath' -includejobs $false
Export-SQLServerObject -inst 'yourinstance' -path 'yourpath' -includejobs 0

The same method works for the rest of the objects by using the other parameters.

The code

The finished code look like this:

<# 
.SYNOPSIS
    Generates export files of SQL Server objects
.DESCRIPTION
    This function will return generate an export file of SQL Server objects to a .sql file.
    This includes server roles, logins, linked servers, triggers, database mail and jobs.
.PARAMETER  instance
    This is the instance that needs to be connected
.PARAMETER path
    Path to export to
.PARAMETER port
    This is the port of the instance that needs to be used
.PARAMETER includetimestamp
    Boolean to include a timestamp directory to export to
.PARAMETER includeroles
    Boolean to include or exclude server roles. Can be value $false/$true or 0/1.
.PARAMETER includelogins
    Boolean to include or exclude logins. Can be value $false/$true or 0/1.
.PARAMETER includelinkedservers
    Boolean to include or exclude linked servers. Can be value $false/$true or 0/1.
.PARAMETER includetriggers
    Boolean to include or exclude triggers. Can be value $false/$true or 0/1.
.PARAMETER includemail
    Boolean to include or exclude database mail. Can be value $false/$true or 0/1.
.PARAMETER includejobs
    Boolean to include or exclude jobs. Can be value $false/$true or 0/1.
.EXAMPLE
    Export-SQLServerObject "SQL01" -path 'C:\Temp\export'
.EXAMPLE
    Export-SQLServerObject -inst "SQL01\INST01" -path 'C:\Temp\export'
.EXAMPLE
    Export-SQLServerObject -inst "SQL01\INST01" 4321 -path 'C:\Temp\export'
.EXAMPLE
    Export-SQLServerObject -inst "SQL01\INST01" -includemail $false -path 'C:\Temp\export'
.INPUTS
.OUTPUTS
    Script files
.NOTES
.LINK
#>
function Export-SQLServerObject
{

    param
    (
        [Parameter(Mandatory = $true, Position=1)]
        [ValidateNotNullOrEmpty()][string]$inst = $null,
        [Parameter(Mandatory = $false, Position=2)]
        [string]$port = '1433',
        [Parameter(Mandatory = $true, Position=3)]
        [ValidateNotNullOrEmpty()][string]$path = $null,
        [Parameter(Mandatory = $false, Position=4)]
        [Alias("timestamp")]
        [bool]$includetimestamp = $true,
        [Parameter(Mandatory = $false, Position=5)]
        [Alias("incr")]
        [bool]$includeroles = $true,
        [Parameter(Mandatory = $false, Position=6)]
        [Alias("incl")]
        [bool]$includelogins = $true,
        [Parameter(Mandatory = $false, Position=7)]
        [Alias("incls")]
        [bool]$includelinkedservers = $true,
        [Parameter(Mandatory = $false, Position=8)]
        [Alias("inct")]
        [bool]$includetriggers = $true,
        [Parameter(Mandatory = $false, Position=9)]
        [Alias("incm")]
        [bool]$includemail = $true,
        [Parameter(Mandatory = $false, Position=10)]
        [Alias("incj")]
        [bool]$includejobs = $true
        
    )

    #Load the assembly
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Create the server object and retrieve the information
    try{
        # Make a connection to the database
        $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$inst,$port"

        Write-Host "Starting SQL Server Export: " $inst -ForegroundColor Green

        # Set the destination
        $destination = "$path\$inst\"

        # Check if timestamp is needed
        if($includetimestamp)
        {
            # Create a timestamp
            $timestamp = Get-Date -Format yyyyMMddHHmmss
            # Set the desitnation
            $destination = "$destination\$timestamp"
        }

        if((Test-Path $destination) -eq $false)
        {
            # Create the directory
            New-Item -ItemType Directory -Path "$destination" | Out-Null
        }

        # Create the variable for holding all the server objects
        [array]$objects = $null

        # Get the roles
        if($includeroles -eq $true)
        {
            Write-Host "Retrieving Server Roles"  -ForegroundColor Green
            $objects += $server.Roles | where {($_.IsFixedRole -eq $false) -and ($_.Name -ne 'public')}
        }

        # Get the logins
        if($includelogins -eq $true)
        {
            Write-Host "Retrieving Logins"  -ForegroundColor Green
            $objects += $server.Logins | where {$_.Name -notmatch 'BUILTIN*|NT SERVICE*|NT AUTHORITY*|##*|sa'}
        }


        # Get the linked servers
        if($includelinkedservers -eq $true)
        {
            Write-Host "Retrieving Linked Servers"  -ForegroundColor Green
            $objects += $server.LinkedServers
        }

        # Get the triggers
        if($includetriggers -eq $true)
        {
            Write-Host "Retrieving Triggers"  -ForegroundColor Green
            $objects += $server.Triggers
        }

        # Get the mail objects
        if($includemail -eq $true)
        {
            Write-Host "Retrieving Database Mail"  -ForegroundColor Green
            $objects += $server.Mail
            $objects += $server.Mail.Accounts
            $objects += $server.Mail.Profiles
        }

        # Get the job objects
        if($includejobs -eq $true)
        {
            Write-Host "Retrieving Jobs"  -ForegroundColor Green
            $objects += $server.JobServer.Operators
            $objects += $server.JobServer.Jobs
            $objects += $server.JobServer.Alerts
        }

        Write-Host $objects.Length "objects found to export." -ForegroundColor Green 

        # Check if there any objects to export
        if($objects.Length -ge 1)
        {
            # Create the scripter object
            $scripter = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") $server #"$inst,$port"

            # Set general options
            $scripter.Options.AppendToFile = $false
            $scripter.Options.AllowSystemObjects = $false
            $scripter.Options.ClusteredIndexes = $true
            $scripter.Options.DriAll = $true
            $scripter.Options.ScriptDrops = $false
            $scripter.Options.IncludeHeaders = $true
            $scripter.Options.ToFileOnly = $true
            $scripter.Options.Indexes = $true
            $scripter.Options.WithDependencies = $false

            foreach($item in $objects )
            {
                # Get the type of object
                $typeDir = $item.GetType().Name

                # Check if the directory for the item type exists
                if((Test-Path "$destination\$typeDir") -eq $false)
                {
                    New-Item -ItemType Directory -Name "$typeDir" -path "$destination" | Out-Null
                }

                #Setup the output file for the item
                $filename = $item -replace "\[|\]"
                
                # Check if the filename contains a "\", if so replace it
                if($filename -match "\\")
                {
                    $filename = $filename -replace "\\", "_"
                }

                $scripter.Options.FileName = "$destination\$typeDir\$filename.sql"

                # Script out the object 
                Write-Host "Scripting out $typeDir $item"
                
                $scripter.Script($item)
            }
        }
    }
    catch [Exception]
    {
        $errorMessage = $_.Exception.Message
        $line = $_.InvocationInfo.ScriptLineNumber
        $script_name = $_.InvocationInfo.ScriptName
        Write-Host "Error: Occurred on line $line in script $script_name." -ForegroundColor Red
        Write-Host "Error: $ErrorMessage" -ForegroundColor Red
    }
}

Export-SQLServerObject -inst 'yourinstance' -path 'yourpath'

The Result

The result is a script that will export all your objects to “.sql” files. The result of the execution of the script can be seen below:

Export-SQLServerObject_working

I hope this helps you out. Any comment or feedback is appreciated.

Leave a Reply