Script Database Objects with PowerShell

Standard

exportI recently did a presentation about documenting SQL Server with PowerShell.

The presentation was initially intended to show people how to get data like configuration settings, databases, database files etc, from SQL Server and export that to an Excel file.

At the end several people had questions how to export database objects using PowerShell. I must admit I didn’t have a direct answer and promised to come back with solution. Luckily Bob Klimes had a solution by using the Scripter functionality in the SMO and even send me one of his scripts.

The Scripter class

I had never used this part of the SMO before and wanted to know what it did, so as I start with reading the documentation. Cool, we have a script function and I can retrieve a type which enables me to automate some things.

The code

Because I like to use functions to do my work I created a function to export the database objects. The end result looks like this:

function Export-DatabaseObject
{
    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)]
        [string]$dblist = 'ALL',
        [Parameter(Mandatory = $false, Position=5)]
        [Alias("timestamp")]
        [bool]$includetimestamp = $true,
        [Parameter(Mandatory = $false, Position=6)]
        [Alias("inct")]
        [bool]$includetables = $true,
        [Parameter(Mandatory = $false, Position=7)]
        [Alias("incv")]
        [bool]$includeviews = $true,
        [Parameter(Mandatory = $false, Position=8)]
        [Alias("incsp")]
        [bool]$includesp = $true,
        [Parameter(Mandatory = $false, Position=9)]
        [Alias("incu")]
        [bool]$includeudf = $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"

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

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

        $databases = @{}

        # Check if a selective list must be used
        if($dblist -eq 'ALL')
        {
            # Get the user databases, the system databases are excluded
            $databases = $server.Databases | Select Name | where {$_.Name -notmatch 'master|model|msdb|tempdb' }
        }
        else
        {
            $databases = @()

            #clean up the data
            $dblist = $dblist.Replace(' ', '')

            # Split the string
            $values = $dblist.Split(',') 

            foreach($value in $values)
            {
                $db = New-Object psobject
                $db | Add-Member -membertype noteproperty -name "Name" -Value $value
                $databases += $db
            }

        }

        # Check if there are any databases
        if($databases.Count -ge 1)
        {
            # Loop through
            foreach($database in $databases)
            {
                Write-Host "Starting Database Export: " $database.Name -ForegroundColor Green

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

                # Create the variable for holding all the database objects
                $objects = $null

                # Check if the tables need to be included
                if($includetables)
                {
                    Write-Host "Retrieving Tables"  -ForegroundColor Green

                    # Get the tables
                    $objects += $server.Databases[$database.Name].Tables | where {!($_.IsSystemObject)}
                }

                # Check if the views need to be included
                if($includeviews)
                {
                    Write-Host "Retrieving Views" -ForegroundColor Green

                    # Get the views
                    $objects += $server.Databases[$database.Name].Views | where {!($_.IsSystemObject)}
                }

                # Check if the stored procedures need to be included
                if($includesp)
                {
                    Write-Host "Retrieving Stored Procedures" -ForegroundColor Green

                    # Get the stored procedures
                    $objects += $server.Databases[$database.Name].StoredProcedures | where {!($_.IsSystemObject)}
                }

                # Check if the user defined functions need to be included
                if($includeudf)
                {
                    Write-Host "Retrieving User Defined Functions" -ForegroundColor Green

                    # Get the stored procedures
                    $objects += $server.Databases[$database.Name].UserDefinedFunctions | where {!($_.IsSystemObject)}
                }

                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 "$dbDestination\$typeDir") -eq $false)
                        {
                            New-Item -ItemType Directory -Name "$typeDir" -Path "$dbDestination" | Out-Null
                        }

                        #Setup the output file for the item
                        $filename = $item -replace "\[|\]"
                        $scripter.Options.FileName = "$dbDestination\$typeDir\$filename.sql"

                        # Script out the object 
                        Write-Host "Scripting out $typeDir $item"
                        $scripter.Script($item)

                    }
                }
            }
        }
        else
        {
            Write-Host "No databases found." -ForegroundColor Magenta
        }
    }
    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-DatabaseObject -inst 'yourserver' -dblist 'yourdatabase' -path 'yourpath'

It needs two parameters to work:

  1. -inst: your instance
  2. -path: The path to export to

There is third parameter I advise you use and that is “-dblist”. This enables you to select a certain database or multiple databases separated by a comma. If this parameter is not set it will export all the objects for all the user databases so be aware of that!

In the end I was pleased with the result and the function could well be implemented in my existing module, PSSQLLib, and so I did.

The result

There is now an extra function in the library called Export-DatabaseObject.

When executed it looks like this:

/home/sstad/domains/sqlstad.nl/public html/wp content/uploads/2016/04/160414 export databaseobject working

It will create a folder structure with the instance name, database, time stamp and at last a directory containing all the files for a specific object type like a table or view.

This made my life easier and again it’s a thing that can be done much faster using a script than using the GUI in SSMS 😉

I hope this can help you out with your daily work. Any comment is appreciated.

 

Leave a Reply