Script SQL Server Objects with PowerShell

Scripting 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 thePSSQLLib 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:

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:

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

稿源:SQL Stad (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Script SQL Server Objects with PowerShell

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录