Script SQL Server Objects with PowerShell

存储架构 2016-04-22

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

责编内容by:SQL Stad (源链)。感谢您的支持!

您可能感兴趣的

SQL SERVER – MSDB Database Uncontrolled Growth Due... Received an email “Need your urgent help On Demand, our MSDB Database has grow...
Support-Friendly Tooling One of the techniques I briefly mentioned in my last post “ Treat All Test E...
Statements prepared by AOP and MSSQL databases do ... I'm having a problem running prepared queries on a MSSQL database using PDO. I c...
When the PowerShell pipeline doesn’t line up The PowerShell Pipeline One of the defining features of PowerShell is the obje...
Using PowerShell to navigate SQL Server as a drive I might be a SQL Server pro, but I’m still a PowerShell novice. I learn best by ...