Modules/MSSql.ps1

function Use-SqlServerModule
{
    <#
        .SYNOPSIS
            Loads and installs the sqlserver module if not already installed or loaded
        
        .DESCRIPTION
            Loads and installs the sqlserver module if not already installed or loaded
        
        .EXAMPLE
            Use-WebAdministration
    #>

    
    if (!(Get-Module -ListAvailable -Name sqlserver)) {
        Install-Module sqlserver -Force -ErrorAction Stop    
    }
    
    if (!(Get-Module sqlserver)) {
        Import-Module sqlserver -ErrorAction Stop -Force 
    }
}


function Use-MicrosoftSqlServerDac
{
    <#
        .SYNOPSIS
            Loads the latest Microsoft.SqlServer.Dac.dll with Add-Type
        
        .DESCRIPTION
            Loads the latest Microsoft.SqlServer.Dac.dll with Add-Type
        
        .EXAMPLE
            Use-MicrosoftSqlServerDac
    #>

    if ("Microsoft.SqlServer.Dac.DacService" -as [type]) {
        $Version = 0
        $MicrosoftSqlServerDacDLLPath = ""
    
        Get-ChildItem "${env:ProgramFiles(x86)}\Microsoft SQL Server\*" -Recurse -Filter "*Microsoft.SqlServer.Dac.dll" |
        ForEach-Object {
            if($Version -lt $_.FullName.split("\")[3]) {
                $Version = $_.FullName.split("\")[3]
                $MicrosoftSqlServerDacDLLPath = $_.FullName
            }   
        }

        #Register the DLL we need
        Add-Type -Path $MicrosoftSqlServerDacDLLPath
    }        
}


function Update-SqlDatabase
{
    <#
        .SYNOPSIS
            Updates a MSSql Database to a given schema from a dacpac file
        
        .DESCRIPTION
            Updates a MSSql Database to a given schema from a dacpac file
        
        .PARAMETER ConnectionString
            Specifies the connection String to the database server e.g.
            "data source=LOCALHOST;Integrated Security=true;"

        .PARAMETER DatabaseName
            Specifies the name of the Database to Update
        
        .PARAMETER DacpacFilePath
            Specifies the path to the dacpac file which contains the latest schema for the database

        .PARAMETER DacDeployOptions
            Optionally specifies DacDeployOptions see
            https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacdeployoptions(v=sql.120).aspx
            for more information

        .EXAMPLE
            Update-SqlDatabase -ConnectionString "data source=LOCALHOST;Integrated Security=true;" -Database "MyDatabase" -DacpacFilePath "C:\MySolution\MyDatabase.dacpac"
    #>

    
    param(
        [Parameter(Mandatory=$true, Position=1)]
        [string]  $ConnectionString     = $null,

        [Parameter(Mandatory=$true, Position=2)]
        [string]  $DatabaseName     = $null,

        [Parameter(Mandatory=$true, Position=3)]
        [string]  $DacpacFilePath     = $null,

        [Parameter(Mandatory=$false, Position=3)]
        [psobject]  $DacDeployOptions    = $null        
    )
    
    Begin {
        Use-MicrosoftSqlServerDac

        if(-Not($DacDeployOptions)) {
            $DeployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
        }
    }
    
    Process {
        $DacService = New-Object Microsoft.SqlServer.dac.dacservices($ConnectionString)
        if(Test-Path -Path $DacpacFilePath) {
            $DacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($DacpacFilePath)
        } else {
            Throw "$DacpacFilePath could not be found"   
        }

        $DacService.deploy($DacPackage, $DatabaseName, $true, $DeployOptions)
    }
    
    End { 
    }
}


function Get-SqlUpdateScript
{
    <#
        .SYNOPSIS
            Returns an update script for a mssql database using a dacpac file
        
        .DESCRIPTION
            Returns an update script for a mssql database using a dacpac file
        
        .PARAMETER ConnectionString
            Specifies the connection String to the database server e.g.
            "data source=LOCALHOST;Integrated Security=true;"

        .PARAMETER DatabaseName
            Specifies the name of the Database to Update
        
        .PARAMETER DacpacFilePath
            Specifies the path to the dacpac file which contains the latest schema for the database

        .PARAMETER DacDeployOptions
            Optionally specifies DacDeployOptions see
            https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dacdeployoptions(v=sql.120).aspx
            for more information

        .EXAMPLE
            Get-SqlUpdateScript -ConnectionString "data source=LOCALHOST;Integrated Security=true;" -Database "MyDatabase" -DacpacFilePath "C:\MySolution\MyDatabase.dacpac"
    #>

    
    param(
        [Parameter(Mandatory=$true, Position=1)]
        [string]  $ConnectionString     = $null,

        [Parameter(Mandatory=$true, Position=2)]
        [string]  $DatabaseName     = $null,

        [Parameter(Mandatory=$true, Position=3)]
        [string]  $DacpacFilePath     = $null,

        [Parameter(Mandatory=$false, Position=3)]
        [psobject]  $DacDeployOptions    = $null        
    )
    
    Begin {
        Use-MicrosoftSqlServerDac

        if(-Not($DacDeployOptions)) {
            $DeployOptions = New-Object Microsoft.SqlServer.Dac.DacDeployOptions
        }
    }
    
    Process {
        $DacService = New-Object Microsoft.SqlServer.dac.dacservices($ConnectionString)
        if(Test-Path -Path $DacpacFilePath) {
            $DacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($DacpacFilePath)
        } else {
            Throw "$DacpacFilePath could not be found"   
        }

        return $DacService.GenerateDeployScript($DacPackage, $DatabaseName, $DeployOptions)
    }
    
    End { 
    }
}