PSRedgate

0.4.5

PSRedgate.psm1

Write-Verbose 'Importing from [C:\projects\psredgate\PSRedgate\private]'
# .\PSRedgate\private\Get-RedgateSQLBackupParameter.ps1
function Get-RedgateSQLBackupParameter
{
    <#
    .SYNOPSIS


    .DESCRIPTION


    .EXAMPLE


    .EXAMPLE

#>
    [CmdletBinding()]
    param (
        [parameter(Mandatory)]
        # This cmdlet will take an array of parameters and put them in the format needed for SQL Backup
        $Parameters,

        [parameter()]
        # This cmdlet will take an array of parameters and put them in the format needed for SQL Backup
        [ValidateSet('SQL', 'CommandLine')]
        $OutputFormat = 'SQL'

    )
    BEGIN { }
    PROCESS
    {
        try
        {
            if ($OutputFormat -eq 'SQL')
            {
                #Build the with string so that it can be added to the command
                foreach ($option in $options.GetEnumerator())
                {
                    $paramType = $option.Value.GetType().Name
                    $paramName = $option.Key.ToString().ToUpper()
                    $paramValue = $option.Value.ToString()

                    if ($paramValue)
                    {
                        if (($paramType -eq 'SwitchParameter'))
                        {
                            $with += "$paramName, "
                        }
                        elseif ($paramName -eq 'PASSWORD')
                        {
                            $unsecureString = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($option.Value)
                            $with += "PASSWORD = ''$([System.Runtime.InteropServices.Marshal]::PtrToStringAuto($unsecureString))'', "
                        }
                        elseif ($paramName -eq 'PASSWORDFILE')
                        {
                            $unsecureString = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($option.Value)
                            $with += "PASSWORD = ''FILE:$([System.Runtime.InteropServices.Marshal]::PtrToStringAuto($unsecureString))'', "
                        }
                        elseif ($paramName -eq 'COPYTO')
                        {
                            $with += "$paramName = ''$($paramValue -join "'', $paramName = ''")'', "
                        }
                        elseif ($paramType -eq 'String' -and $paramName -notlike 'ERASEFILES*')
                        {
                            if ($paramName.Contains('-'))
                            {
                                $with += "$($paramName.Replace('-', ' ')) ''$paramValue'', "
                            }
                            else
                            {
                                $with += "$paramName = ''$paramValue'', "
                            }
                        }
                        else
                        {
                            $with += "$($paramName.Replace('-','')) = $paramValue, "
                        }
                    }
                }

                #Strip off the trailing comma and space
                if ($options.count -gt 0)
                {
                    $with = $with.TrimEnd(', ')
                }

                Write-Output "WITH $with"
            }
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break;
        }
    }
    END { }
}
# .\PSRedgate\private\Get-SQLServerDatabaseFile.ps1
function Get-SQLServerDatabaseFile
{
    <#
    .SYNOPSIS
        This cmdlet will return a collection outlining all of the files on disk used by a database.

    .DESCRIPTION
        If we need a hash with the name of the database and the location of the files that are being used by the database. The output looks like this:
        [
            DatabaseName : ExampleDB
            FileType : LOG
            DatabaseFileType : Logs
            DatabaseFileLocation : L:\EX1\ExampleDB_log.ldf
            DatabaseFileName : ExampleDB_log.ldf
            DatabaseFileNameNoExt : ExampleDB_log
            FileOrdinal : 1

            DatabaseName : ExampleDB
            FileType : ROWS
            DatabaseFileType : Data
            DatabaseFileLocation : M:\EX1\ExampleDB.mdf
            DatabaseFileName : ExampleDB.mdf
            DatabaseFileNameNoExt : ExampleDB
            FileOrdinal : 1
        ]
        There might be more than one file for either of these. The file ordinal will tell you the file index.

    .EXAMPLE
        Get-SQLServerDatabaseFile -SQLServerName SQLEXAMPLE1 -DatabaseName ExampleDB

        Return the files for the database specified on the sql server provided.

    .EXAMPLE
        Get-SQLServerDatabaseFile -SQLServerName SQLEXAMPLE1

        Return the files used by all databases in use on the sql server provided.

#>
    [CmdletBinding()]
    param (
        [parameter(Mandatory)]
        [Alias('ServerInstance')]
        # Name of the sql server you want to connect to and examine the files of.
        [string] $SQLServerName,

        [parameter()]
        [Alias('Database')]
        # Name of the database you would like to retrieve the files for
        [string] $DatabaseName,

        [parameter()]
        # If you would like to execute this as a different user and not use integrated auth, pass in a credential object to run as a different user.
        [System.Management.Automation.PSCredential] $Credential
    )
    BEGIN { }
    PROCESS
    {
        try
        {
            # this is some database hackery that I wrote to help me find the files. There is probably an SMO way that you could get this info, but I'm a DBA. :)
            $query = "WITH database_file_names AS (
                            SELECT db.name AS DatabaseName
                                , mf.type_desc AS FileType
                                , mf.name AS LogicalName
                                , mf.physical_name AS DBFileLocation
                                , REVERSE( LEFT(REVERSE( mf.physical_name ), PATINDEX( '%\%', REVERSE( mf.physical_name )) - 1)) AS DBFileName
                            FROM sys.master_files mf
                                    INNER JOIN sys.databases db
                                        ON db.database_id = mf.database_id
                        )
                        SELECT dbfn.DatabaseName
                                , dbfn.FileType
                                , IIF(dbfn.FileType = 'ROWS', 'Data', 'Log') AS DatabaseFileType
                                , dbfn.DBFileLocation AS DatabaseFileLocation
                                , dbfn.LogicalName AS DatabaseLogicalName
                                , dbfn.DBFileName AS DatabaseFileName
                                , REPLACE( REPLACE( REPLACE( dbfn.DBFileName, '.mdf', '' ), '.ldf', '' ), '.ndf', '' ) AS DatabaseFileNameNoExt
                                , ROW_NUMBER() OVER ( PARTITION BY dbfn.DatabaseName
                                                                , dbfn.FileType
                                                        ORDER BY dbfn.DBFileLocation
                                ) AS FileOrdinal
                        FROM database_file_names dbfn
                        WHERE (
                                1 = 1
                                $(if($DatabaseName){
                                    "AND dbfn.DatabaseName = '$DatabaseName'"
                                })
                            )"

            Write-Debug $query
            $result = Invoke-Sqlcmd2 -ServerInstance $SQLServerName -Database 'master' -Query $query -Credential $Credential
            Write-Output $result
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break;
        }
    }
    END { }
}
# .\PSRedgate\private\Get-SQLServerDefaultFileLocation.ps1
function Get-SQLServerDefaultFileLocation
{
    <#
    .SYNOPSIS
        This will return the default location of the files used by sql server such as data, log, and backup.
    .DESCRIPTION
        This cmdlet is useful for figuring out where a sql server is installed and where it is keeping it's data.
    .EXAMPLE
        Get-SQLServerDefaultFileLocation -SQLServerName SQLEXAMPLE1

        Will return a hash table that will look like this:

        {
            DefaultData = 'D:\EXAMPLE1'
            DefaultLog = 'L:\EXAMPLE1'
            DefaultBackup = 'C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Backup'
        }

#>
    [CmdletBinding()]
    param (
        [parameter(Mandatory)]
        [Alias('ServerInstance')]
        # The server you want to create a remote connection to
        [string] $SQLServerName,

        [parameter()]
        # If you would like to execute this as a different user and not use integrated auth, pass in a credential object to run as a different user.
        [System.Management.Automation.PSCredential] $Credential
    )
    BEGIN {}
    PROCESS
    {
        try
        {
            $query = "DECLARE @DefaultData NVARCHAR(512)
                        , @DefaultLog NVARCHAR(512)
                        , @DefaultBackup NVARCHAR(512)
                        , @MasterData NVARCHAR(512)
                        , @MasterLog NVARCHAR(512);

                        EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                                        , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                                        , N'DefaultData'
                                                        , @DefaultData OUTPUT;

                        EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                                        , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                                        , N'DefaultLog'
                                                        , @DefaultLog OUTPUT;

                        EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                                        , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                                        , N'BackupDirectory'
                                                        , @DefaultBackup OUTPUT;

                        EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                                        , N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
                                                        , N'SqlArg0'
                                                        , @MasterData OUTPUT;

                        SELECT @MasterData = SUBSTRING( @MasterData, 3, 255 );
                        SELECT @MasterData = SUBSTRING( @MasterData, 1, LEN( @MasterData ) - CHARINDEX( '\', REVERSE( @MasterData )));

                        EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE'
                                                        , N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
                                                        , N'SqlArg2'
                                                        , @MasterLog OUTPUT;

                        SELECT @MasterLog = SUBSTRING( @MasterLog, 3, 255 );
                        SELECT @MasterLog = SUBSTRING( @MasterLog, 1, LEN( @MasterLog ) - CHARINDEX( '\', REVERSE( @MasterLog )));

                        SELECT ISNULL( @DefaultData, @MasterData ) AS DefaultData
                              , ISNULL( @DefaultLog, @MasterLog ) AS DefaultLog
                              , ISNULL( @DefaultBackup, @MasterLog ) AS DefaultBackup;"

            Write-Debug $query
            $result = Invoke-Sqlcmd2 -ServerInstance $SQLServerName -Database 'master' -Query $query -Credential $Credential
            Write-Output $result

        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break
        }
    }
    END {}
}
# .\PSRedgate\private\Invoke-Sqlcmd2.ps1
function Invoke-Sqlcmd2
{
    <#
    .SYNOPSIS
        Runs a T-SQL script.

    .DESCRIPTION
        Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified.
        Paramaterized queries are supported.

        Help details below borrowed from Invoke-Sqlcmd & Invoke-SQLcmd2, literally just changed the name - JW

    .PARAMETER ServerInstance
        One or more ServerInstances to query. For default instances, only specify the computer name: "MyComputer". For named instances, use the format "ComputerName\InstanceName".

    .PARAMETER Database
        A character string specifying the name of a database. Invoke-Sqlcmd2 connects to this database in the instance that is specified in -ServerInstance.

        If a SQLConnection is provided, we explicitly switch to this database

    .PARAMETER Query
        Specifies one or more queries to be run. The queries can be Transact-SQL (? or XQuery statements, or sqlcmd commands. Multiple queries separated by a semicolon can be specified. Do not specify the sqlcmd GO separator. Escape any double quotation marks included in the string ?). Consider using bracketed identifiers such as [MyTable] instead of quoted identifiers such as "MyTable".

    .PARAMETER InputFile
        Specifies a file to be used as the query input to Invoke-Sqlcmd2. The file can contain Transact-SQL statements, (? XQuery statements, and sqlcmd commands and scripting variables ?). Specify the full path to the file.

    .PARAMETER Credential
        Specifies A PSCredential for SQL Server Authentication connection to an instance of the Database Engine.

        If -Credential is not specified, Invoke-Sqlcmd attempts a Windows Authentication connection using the Windows account running the PowerShell session.

        SECURITY NOTE: If you use the -Debug switch, the connectionstring including plain text password will be sent to the debug stream.

    .PARAMETER Encrypt
        If specified, will request that the connection to the SQL is done over SSL. This requires that the SQL Server has been set up to accept SSL requests. For information regarding setting up SSL on SQL Server, visit this link: https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

    .PARAMETER QueryTimeout
        Specifies the number of seconds before the queries time out.

    .PARAMETER ConnectionTimeout
        Specifies the number of seconds when Invoke-Sqlcmd2 times out if it cannot successfully connect to an instance of the Database Engine. The timeout value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

    .PARAMETER As
        Specifies output type - DataSet, DataTable, array of DataRow, PSObject or Single Value

        PSObject output introduces overhead but adds flexibility for working with results: http://powershell.org/wp/forums/topic/dealing-with-dbnull/

    .PARAMETER SqlParameters
        Hashtable of parameters for parameterized SQL queries. http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/

        Example:
            -Query "SELECT ServerName FROM tblServerInfo WHERE ServerName LIKE @ServerName"
            -SqlParameters @{"ServerName = "c-is-hyperv-1"}

    .PARAMETER AppendServerInstance
        If specified, append the server instance to PSObject and DataRow output

    .PARAMETER SQLConnection
        If specified, use an existing SQLConnection.
            We attempt to open this connection if it is closed

    .INPUTS
        None
            You cannot pipe objects to Invoke-Sqlcmd2

    .OUTPUTS
       As PSObject: System.Management.Automation.PSCustomObject
       As DataRow: System.Data.DataRow
       As DataTable: System.Data.DataTable
       As DataSet: System.Data.DataTableCollectionSystem.Data.DataSet
       As SingleValue: Dependent on data type in first column.

    .EXAMPLE
        Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"

        This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
        StartTime
        -----------
        2010-08-12 21:21:03.593

    .EXAMPLE
        Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"

        This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.

    .EXAMPLE
        Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose

        This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
        VERBOSE: hello world

    .EXAMPLE
        Invoke-Sqlcmd2 -ServerInstance MyServer\MyInstance -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo" -as PSObject | ?{$_.VCNumCPU -gt 8}
        Invoke-Sqlcmd2 -ServerInstance MyServer\MyInstance -Query "SELECT ServerName, VCNumCPU FROM tblServerInfo" -as PSObject | ?{$_.VCNumCPU}

        This example uses the PSObject output type to allow more flexibility when working with results.

        If we used DataRow rather than PSObject, we would see the following behavior:
            Each row where VCNumCPU does not exist would produce an error in the first example
            Results would include rows where VCNumCPU has DBNull value in the second example

    .EXAMPLE
        'Instance1', 'Server1/Instance1', 'Server2' | Invoke-Sqlcmd2 -query "Sp_databases" -as psobject -AppendServerInstance

        This example lists databases for each instance. It includes a column for the ServerInstance in question.
            DATABASE_NAME DATABASE_SIZE REMARKS ServerInstance
            ------------- ------------- ------- --------------
            REDACTED 88320 Instance1
            master 17920 Instance1
            ...
            msdb 618112 Server1/Instance1
            tempdb 563200 Server1/Instance1
            ...
            OperationsManager 20480000 Server2

    .EXAMPLE
        #Construct a query using SQL parameters
            $Query = "SELECT ServerName, VCServerClass, VCServerContact FROM tblServerInfo WHERE VCServerContact LIKE @VCServerContact AND VCServerClass LIKE @VCServerClass"

        #Run the query, specifying values for SQL parameters
            Invoke-Sqlcmd2 -ServerInstance SomeServer\NamedInstance -Database ServerDB -query $query -SqlParameters @{ VCServerContact="%cookiemonster%"; VCServerClass="Prod" }

            ServerName VCServerClass VCServerContact
            ---------- ------------- ---------------
            SomeServer1 Prod cookiemonster, blah
            SomeServer2 Prod cookiemonster
            SomeServer3 Prod blah, cookiemonster

    .EXAMPLE
        Invoke-Sqlcmd2 -SQLConnection $Conn -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"

        This example uses an existing SQLConnection and runs a basic T-SQL query against it

        StartTime
        -----------
        2010-08-12 21:21:03.593


    .NOTES
        Version History
        poshcode.org - http://poshcode.org/4967
        v1.0 - Chad Miller - Initial release
        v1.1 - Chad Miller - Fixed Issue with connection closing
        v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
        v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
        v1.4 - Justin Dearing <zippy1981 _at_ gmail.com> - Added the ability to pass parameters to the query.
        v1.4.1 - Paul Bryson <atamido _at_ gmail.com> - Added fix to check for null values in parameterized queries and replace with [DBNull]
        v1.5 - Joel Bennett - add SingleValue output option
        v1.5.1 - RamblingCookieMonster - Added ParameterSets, set Query and InputFile to mandatory
        v1.5.2 - RamblingCookieMonster - Added DBNullToNull switch and code from Dave Wyatt. Added parameters to comment based help (need someone with SQL expertise to verify these)

        github.com - https://github.com/RamblingCookieMonster/PowerShell
        v1.5.3 - RamblingCookieMonster - Replaced DBNullToNull param with PSObject Output option. Added credential support. Added pipeline support for ServerInstance. Added to GitHub
                                             - Added AppendServerInstance switch.
                                             - Updated OutputType attribute, comment based help, parameter attributes (thanks supersobbie), removed username/password params
                                             - Added help for sqlparameter parameter.
                                             - Added ErrorAction SilentlyContinue handling to Fill method
        v1.6.0 - Added SQLConnection parameter and handling. Is there a more efficient way to handle the parameter sets?
                                             - Fixed SQLConnection handling so that it is not closed (we now only close connections we create)

    .LINK
        https://github.com/RamblingCookieMonster/PowerShell

    .LINK
        New-SQLConnection

    .LINK
        Invoke-SQLBulkCopy

    .LINK
        Out-DataTable

    .FUNCTIONALITY
        SQL
    #>

    [CmdletBinding( DefaultParameterSetName = 'Ins-Que' )]
    [Diagnostics.CodeAnalysis.SuppressMessage("PSPossibleIncorrectComparisonWithNull", '')]
    [OutputType([System.Management.Automation.PSCustomObject], [System.Data.DataRow], [System.Data.DataTable], [System.Data.DataTableCollection], [System.Data.DataSet])]
    param(
        [Parameter( ParameterSetName = 'Ins-Que',

            Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false,
            HelpMessage = 'SQL Server Instance required...' )]
        [Parameter( ParameterSetName = 'Ins-Fil',

            Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false,
            HelpMessage = 'SQL Server Instance required...' )]
        [Alias( 'Instance', 'Instances', 'ComputerName', 'Server', 'Servers', 'sqlserver', 'SQLServerName' )]
        [ValidateNotNullOrEmpty()]
        [string[]]
        $ServerInstance,

        [Parameter(
            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [Alias('dbname')]
        [string]
        $Database,

        [Parameter( ParameterSetName = 'Ins-Que',

            Mandatory = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [Parameter( ParameterSetName = 'Con-Que',

            Mandatory = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [Alias('qry')]
        [string]
        $Query,

        [Parameter( ParameterSetName = 'Ins-Fil',

            Mandatory = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [Parameter( ParameterSetName = 'Con-Fil',

            Mandatory = $true,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [ValidateScript( { Test-Path $_ })]
        [string]
        $InputFile,

        [Parameter( ParameterSetName = 'Ins-Que',

            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [Parameter( ParameterSetName = 'Ins-Fil',

            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false)]
        [System.Management.Automation.PSCredential]
        $Credential,

        [Parameter( ParameterSetName = 'Ins-Que',

            Mandatory = $false,
            ValueFromRemainingArguments = $false)]
        [Parameter( ParameterSetName = 'Ins-Fil',

            Mandatory = $false,
            ValueFromRemainingArguments = $false)]
        [switch]
        $Encrypt,

        [Parameter(
            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [Int32]
        $QueryTimeout = 600,

        [Parameter( ParameterSetName = 'Ins-Fil',

            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [Parameter( ParameterSetName = 'Ins-Que',

            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [Int32]
        $ConnectionTimeout = 15,

        [Parameter(
            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [ValidateSet("DataSet", "DataTable", "DataRow", "PSObject", "SingleValue")]
        [string]
        $As = "DataRow",

        [Parameter(
            Mandatory = $false,
            ValueFromPipelineByPropertyName = $true,
            ValueFromRemainingArguments = $false )]
        [System.Collections.IDictionary]
        $SqlParameters,

        [Parameter(
            Mandatory = $false )]
        [switch]
        $AppendServerInstance,

        [Parameter( ParameterSetName = 'Con-Que',

            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ValueFromRemainingArguments = $false )]
        [Parameter( ParameterSetName = 'Con-Fil',

            Mandatory = $false,
            ValueFromPipeline = $false,
            ValueFromPipelineByPropertyName = $false,
            ValueFromRemainingArguments = $false )]
        [Alias( 'Connection', 'Conn' )]
        [ValidateNotNullOrEmpty()]
        [System.Data.SqlClient.SQLConnection]
        $SQLConnection
    )

    Begin
    {
        if ($InputFile)
        {
            $filePath = $(Resolve-Path $InputFile).path
            $Query = [System.IO.File]::ReadAllText("$filePath")
        }
        $VerbosePreference = 'SilentlyContinue'

        Write-Verbose "Running Invoke-Sqlcmd2 with ParameterSet '$($PSCmdlet.ParameterSetName)'. Performing query '$Query'"

        If ($As -eq "PSObject")
        {
            #This code scrubs DBNulls. Props to Dave Wyatt
            $cSharp = @'
                using System;
                using System.Data;
                using System.Management.Automation;

                public class DBNullScrubber
                {
                    public static PSObject DataRowToPSObject(DataRow row)
                    {
                        PSObject psObject = new PSObject();

                        if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
                        {
                            foreach (DataColumn column in row.Table.Columns)
                            {
                                Object value = null;
                                if (!row.IsNull(column))
                                {
                                    value = row[column];
                                }

                                psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
                            }
                        }

                        return psObject;
                    }
                }
'@

            Try
            {
                Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data', 'System.Xml' -ErrorAction stop
            }
            Catch
            {
                If (-not $_.ToString() -like "*The type name 'DBNullScrubber' already exists*")
                {
                    Write-Warning "Could not load DBNullScrubber. Defaulting to DataRow output: $_"
                    $As = "Datarow"
                }
            }
        }

        #Handle existing connections
        if ($PSBoundParameters.ContainsKey('SQLConnection'))
        {
            if ($SQLConnection.State -notlike "Open")
            {
                Try
                {
                    Write-Verbose "Opening connection from '$($SQLConnection.State)' state"
                    $SQLConnection.Open()
                }
                Catch
                {
                    Throw $_
                }
            }

            if ($Database -and $SQLConnection.Database -notlike $Database)
            {
                Try
                {
                    Write-Verbose "Changing SQLConnection database from '$($SQLConnection.Database)' to $Database"
                    $SQLConnection.ChangeDatabase($Database)
                }
                Catch
                {
                    Throw "Could not change Connection database '$($SQLConnection.Database)' to $Database`: $_"
                }
            }

            if ($SQLConnection.state -like "Open")
            {
                $ServerInstance = @($SQLConnection.DataSource)
            }
            else
            {
                Throw "SQLConnection is not open"
            }
        }

    }
    Process
    {
        foreach ($SQLInstance in $ServerInstance)
        {
            Write-Verbose "Querying ServerInstance '$SQLInstance'"

            if ($PSBoundParameters.Keys -contains "SQLConnection")
            {
                $Conn = $SQLConnection
            }
            else
            {
                if ($Credential)
                {
                    $ConnectionString = "Server={0};Database={1};User ID={2};Password=`"{3}`";Trusted_Connection=False;Connect Timeout={4};Encrypt={5}" -f $SQLInstance, $Database, $Credential.UserName, $Credential.GetNetworkCredential().Password, $ConnectionTimeout, $Encrypt
                }
                else
                {
                    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2};Encrypt={3}" -f $SQLInstance, $Database, $ConnectionTimeout, $Encrypt
                }

                $conn = New-Object System.Data.SqlClient.SQLConnection
                $conn.ConnectionString = $ConnectionString
                Write-Verbose "ConnectionString $ConnectionString"

                Try
                {
                    $conn.Open()
                }
                Catch
                {
                    Write-Error $_
                    continue
                }
            }

            #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
            if ($PSBoundParameters.Verbose)
            {
                $conn.FireInfoMessageEventOnUserErrors = $true
                $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
                $conn.add_InfoMessage($handler)
            }

            $cmd = New-Object system.Data.SqlClient.SqlCommand($Query, $conn)
            $cmd.CommandTimeout = $QueryTimeout

            if ($SqlParameters -ne $null)
            {
                $SqlParameters.GetEnumerator() |
                    ForEach-Object {
                    If ($_.Value -ne $null)
                    { $cmd.Parameters.AddWithValue($_.Key, $_.Value) }
                    Else
                    { $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) }
                } > $null
            }

            $ds = New-Object system.Data.DataSet
            $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

            Try
            {
                [void]$da.fill($ds)
                if (-not $PSBoundParameters.ContainsKey('SQLConnection'))
                {
                    $conn.Close()
                }
            }
            Catch
            {
                $Err = $_
                if (-not $PSBoundParameters.ContainsKey('SQLConnection'))
                {
                    $conn.Close()
                }

                switch ($ErrorActionPreference.tostring())
                {
                    {'SilentlyContinue', 'Ignore' -contains $_} {}
                    'Stop' {     Throw $Err }
                    'Continue' { Write-Error $Err}
                    Default {    Write-Error $Err}
                }
            }

            if ($AppendServerInstance)
            {
                #Basics from Chad Miller
                $Column = New-Object Data.DataColumn
                $Column.ColumnName = "ServerInstance"
                $ds.Tables[0].Columns.Add($Column)
                Foreach ($row in $ds.Tables[0])
                {
                    $row.ServerInstance = $SQLInstance
                }
            }

            switch ($As)
            {
                'DataSet'
                {
                    $ds
                }
                'DataTable'
                {
                    $ds.Tables
                }
                'DataRow'
                {
                    $ds.Tables[0]
                }
                'PSObject'
                {
                    #Scrub DBNulls - Provides convenient results you can use comparisons with
                    #Introduces overhead (e.g. ~2000 rows w/ ~80 columns went from .15 Seconds to .65 Seconds - depending on your data could be much more!)
                    foreach ($row in $ds.Tables[0].Rows)
                    {
                        [DBNullScrubber]::DataRowToPSObject($row)
                    }
                }
                'SingleValue'
                {
                    $ds.Tables[0] | Select-Object -ExpandProperty $ds.Tables[0].Columns[0].ColumnName
                }
            }
        }
    }
    end { }
}
Write-Verbose 'Importing from [C:\projects\psredgate\PSRedgate\public]'
# .\PSRedgate\public\Backup-RedgateDatabase.ps1
function Backup-RedgateDatabase
{
    <#
    .SYNOPSIS
    Short description

    .DESCRIPTION
    Long description

    .EXAMPLE
    An example

    .NOTES
    General notes
    #>

    [CmdletBinding(SupportsShouldProcess, DefaultParameterSetName = 'Default')]
    Param (
        [Parameter(Mandatory)]
        # The name of the server containing the database you want to backup
        [string] $SQLServerName,

        [Parameter(Mandatory)]
        # The name of the database you want to backup.
        [string] $DatabaseName,

        [Parameter(Mandatory)]
        [ValidateSet('FULL', 'DIFF', 'LOG')]
        # The location that you want this file backed up.
        [string] $Type,

        [Parameter(Mandatory)]
        # The location that you want this file backed up. This can include any of the dynamic name parts defined by redgate at http://redgateplace.com
        [string] $Disk,

        [Parameter()]
        # The name you would like the file to have on disk. This can include any of the dynamic name parts defined at http://redgateplace.com
        [string] $FileName = '<TYPE>_<DATABASE>_<DATETIME yyyy_mm_dd_hh_nn_ss>.sqb',

        [Parameter()]
        # To make it easier to identify files that are encrypted, I will put a suffix on the file of _ENCRYPTED. The restore command will detect this and auto prompt for password if you don't provide one.
        [switch] $IncludeEncryptionSuffix,

        [parameter()]
        # If you want to encrypt this backup, provide a credential object containing a password to encrypt.
        [System.Management.Automation.PSCredential] $EncryptionCredential,

        [parameter()]
        # If you would like to execute this as a different user and not use integrated auth, pass in a credential object to run as a different user.
        [System.Management.Automation.PSCredential] $Credential,

        [parameter()]
        # This flag indicates that the file is encrypted and we should pass the password to the cmdlet.
        [switch] $Encrypted,

        [Parameter()]
        # If this command should execute the backup, or output the command.
        [switch] $Execute,

        [Parameter()]
        #Specifies the password to be used with encrypted backup files.
        [SecureString] $PASSWORD,

        [Parameter()]
        #Specifies the password file to be used with encrypted backup files.
        [SecureString] $PASSWORDFILE,

        [Parameter()]
        # The description of the backup file
        [string] $DESCRIPTION,

        [Parameter()]
        # The location that you want this file backup copied to. Can be multiples separated by commas.
        $COPYTO,

        [Parameter()]
        [ValidateRange(0, 8)]
        # Specifies the compression level. The default value is 1.
        [int] $COMPRESSION = 1,

        [Parameter()]
        [ValidateRange(1, 120)]
        # The time interval between retries, in seconds, following a failed data-transfer operation.
        [int] $DISKRETRYINTERVAL = 30,

        [Parameter()]
        [ValidateRange(1, 20)]
        # The maximum number of times to retry a failed data-transfer operation.
        [int] $DISKRETRYCOUNT = 10,

        [Parameter()]
        [ValidateRange(2, 32)]
        # Specifies the number of threads to be used to create the backup, where n is an integer between 2 and 32 inclusive.
        [int] $THREADCOUNT = 2,

        [Parameter()]
        [ValidateRange(0, 6)]
        # The maximum number of times to retry a failed data-transfer operation.
        [int] $THREADPRIORITY = 3,

        [Parameter()]
        [ValidateRange(1, 360)]
        # The number of days to retain the backup files before beginning to erase them
        [int] $ERASEFILES,

        [Parameter()]
        [ValidateRange(1, 360)]
        # The number of days to retain the backup files before beginning to erase them
        [int] $ERASEFILES_PRIMARY,

        [Parameter()]
        # Specifies a copy-only backup.
        [switch] $COPY_ONLY,

        [Parameter()]
        # Specifies that files with the same name in the primary backup folder should be overwritten.
        [switch] $INIT,

        [Parameter()]
        #Specifies whether a backup checksum should be created.
        [switch] $CHECKSUM,

        [Parameter()]
        #This option specifies that a full backup should be created if required to take a differential or transaction log backup.
        [switch] $FULLIFREQUIRED,

        [Parameter()]
        #Specifies that a log file should only be created if SQL Backup Pro encounters an error during the backup process, or the backup completes successfully but with warnings.
        [switch] $LOG_ONERROR,

        [Parameter()]
        #Specifies that a log file should only be created if SQL Backup Pro encounters an error during the backup process.
        [switch] $LOG_ONERRORONLY
    )
    BEGIN { }
    PROCESS
    {
        try
        {
            #These are the command line options that are not used as options and are not needed below
            $configuration = @(
                'SQLServerName'
                'Type'
                'DatabaseName'
                'Disk'
                'Encrypted'
                'Credential'
                'EncryptionCredential'
                'Execute'
                'CommandLine'
                'Replication'
            )

            $options = [ordered]@{}

            Write-Verbose "Getting default file locations for server $SQLServerName"
            $defaultTargetLocations = Get-SQLServerDefaultFileLocation -SQLServerName $SQLServerName

            # if you passed a credential object, we'll put it into the password field.
            if ($EncryptionCredential -and -not($options.Contains('PASSWORD')))
            {
                Write-Verbose "Retrieving the password from the encryption object and adding PASSWORD to the options list."
                $options.Add('PASSWORD', $EncryptionCredential.Password)
            }

            if (-not($Encrypted))
            {
                Write-Verbose 'Adding PASSWORD and PASSWORDFILE to the config array so that it will get stripped out.'
                $configuration += 'PASSWORD'
                $configuration += 'PASSWORDFILE'
            }

            Write-Verbose 'Removing unnecessary parameters from our option array'
            $options = $PSBoundParameters.GetEnumerator() | Where-Object {$PSItem.Key -notin $configuration}

            $arguments = Get-RedgateSQLBackupParameter -Parameters $options

            $backupType = $null
            switch ($Type)
            {
                'FULL' { $backupType = 'DATABASE'}
                'DIFF' { $backupType = 'DATABASE'; $arguments += ' DIFFERENTIAL, ' }
                'LOG' { $backupType = 'LOG'}
            }

            if ($Encrypted -and -not($EncryptionCredential -or $PASSWORD -or $PASSWORDFILE))
            {
                Write-Verbose 'We need a credential because this file needs to be encrypted, prompt for a credential object.'
                $EncryptionCredential = (Get-Credential -UserName 'SQL Backup' -Message 'Enter password to encrypt backup file.')
            }


            Write-Verbose 'Defaulting disk to the default backup location for the server if there is no disk location supplied.'
            if (-not($Disk))
            {
                $Disk = $defaultTargetLocations.DefaultBackup
            }


            if (($PASSWORD -or $PASSWORDFILE) -and ($IncludeEncryptionSuffix))
            {
                Write-Verbose 'Adding _ENCRYPTED suffix to file name so that you can detect encryption automatically.'
                $Disk = $Disk.Replace('.sqb', '_ENCRYPTED.sqb')
            }

            $FullPath = " TO DISK = ''$Disk''"

            $backupCommand = "EXEC master..sqlbackup '-SQL ""BACKUP $backupType [$DatabaseName] $FullPath $arguments""'"

            if ($PSCmdlet.ShouldProcess($SQLServerName, $backupCommand))
            {
                $command = "
                    DECLARE @errorcode INT
                          , @sqlerrorcode INT
                    $($backupCommand), @errorcode OUTPUT, @sqlerrorcode OUTPUT;
                    IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
                    BEGIN
                        RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
                    END"

                $params = @{
                    ServerInstance = $SQLServerName
                    Database       = 'master'
                    Query          = $command
                    Credential     = $Credential
                    QueryTimeout   = 7200
                    As             = 'SingleValue'
                }

                #This will output the command in a formatted way
                Write-Verbose ("`n`nBacking up database $DatabaseName from $SQLServerName`n")
                Write-Verbose ($command.Replace(',', "`n,") | Out-String)

                try
                {
                    $result = Invoke-Sqlcmd2 @params -ErrorAction Stop
                }
                catch
                {
                    # exception message comes through like this: Exception calling "Fill" with "1" argument(s): "SQL Backup failed with exit code: 850; SQL error code: 0"
                    # we want to get the SQL Backup exit code, and the SQL error code.
                    $message = $PSItem.Exception.Message.TrimEnd('"')
                    $message = $message.Split('"')[-1]
                    $errors = $message.Split(';').Trim()
                    foreach ($item in $errors)
                    {
                        $properties = $item.Split(':').Trim()
                        $errorLabel = $properties[0]
                        $errorNumber = $properties[1]
                        if ( $errorLabel -eq 'SQL Backup failed with exit code' -and $errorNumber -ne 0)
                        {
                            Write-Warning "SQL Backup failed with exit code: `n`n$(Get-RedgateSQLBackupError -ErrorNumber $errorNumber)"
                        }
                        elseif ($errorNumber -gt 0)
                        {
                            Write-Warning "$errorLabel : `n`n $errorNumber"
                        }
                    }
                    throw [System.Exception] 'There were errors performing the restore. See warnings above.'
                }
                Write-Verbose ($result | Out-String)
            }
        }
        catch
        {
            Write-Output  $PSItem.Exception | Format-List -Force
            break
        }
    }
    END {}
}
# .\PSRedgate\public\Get-RedgateBackupFileInfo.ps1
function Get-RedgateBackupFileInfo
{
    <#
    .SYNOPSIS
    This cmdlet will return information stored in the backup file.

    .DESCRIPTION
    This is useful for determining things like what server the database backup originated from, when it was taken, how large it was, ect.

    .EXAMPLE
    Get-RedgateBackupInfo -SQLServerName SQLEXAMPLE1 -BackupFile 'C:\Backups\my_db.sqb'

    This will return the basic info stored in the backup file. The SQL Server name is required because this cmdlet uses the master..sqlbackup stored procedure. It has nothing to do with the source db.

    .EXAMPLE
    Get-RedgateBackupInfo -SQLServerName SQLEXAMPLE1 -BackupFile 'C:\Backups\my_db_encrypted.sqb'

    Will attempt to decrypt the backup file using and return the standard information. This will parse the name and if it finds _encrypted it will try to decrypt it. If you did not provide a credential object, a password will be requested.

    .EXAMPLE
    Get-RedgateBackupInfo -SQLServerName SQLEXAMPLE1 -BackupFile 'C:\Backups\my_db.sqb' -Encrypted

    Will attempt to decrypt the backup file using and return the standard information. If you did not provide a credential object, a password will be requested.

    .EXAMPLE
    Get-RedgateBackupInfo -SQLServerName SQLEXAMPLE1 -BackupFile 'C:\Backups\my_db.sqb' -Credential $credential

    Will return the information that was stored in the file. This will use the credentials passed in to execute the query on the SQL Server.

    .NOTES
    General notes
    #>
    [CmdletBinding()]
    param(
        [parameter()]
        # This is the name of the SQL Server where SQL Backup is installed. This cmdlet needs to run the master..sqlbackup stored procedure so provide a server where it is installed.
        [string] $SQLServerName,

        [parameter(ValueFromPipeline)]
        # The full path to the files that you would like to examine and receive information on.
        [string] $BackupFile,

        [parameter()]
        # If the backup is encrypted, pass a credential object with the password needed to decrypt the file.
        [System.Management.Automation.PSCredential] $DecryptionCredential,

        [parameter()]
        # If you would like to execute this as a different user and not use integrated auth, pass in a credential object to run as a different user.
        [System.Management.Automation.PSCredential] $Credential,

        [parameter()]
        # This flag indicates that the file is encrypted and we should pass the password to the cmdlet. This requires all files passed in the pipeline to be encrypted.
        [switch] $Encrypted
    )
    BEGIN {}
    PROCESS
    {

        $with = 'WITH '

        $Encrypted = ($BackupFile.Split('_')[-1] -eq 'ENCRYPTED.sqb' -or $Encrypted)

        if ($Encrypted -and -not($DecryptionCredential))
        {
            $DecryptionCredential = (Get-Credential -UserName 'SQL Backup' -Message 'Enter password to decrypt backup file.')
        }

        if ($Encrypted)
        {
            $with += "PASSWORD = ''$($DecryptionCredential.GetNetworkCredential().Password)'',"
        }

        $with += ' SINGLERESULTSET'

        $query = "EXEC master..sqlbackup '-sql ""RESTORE SQBHEADERONLY FROM DISK = [$BackupFile] $with""'"

        Write-Debug $query
        $result = Invoke-Sqlcmd2 -ServerInstance $SQLServerName -Database 'master' -Query $query -As SingleValue -Credential $Credential

        $fileInfo = @{}

        foreach ($prop in $result.Split("`n"))
        {
            $prop = $prop.split(':', 2)
            if ($prop.Count -gt 1)
            {
                [string]$key = $prop[0]
                [string]$value = $prop[1]

                if ($key -ne '' -and $value -ne '')
                {
                    $key = (Get-Culture).TextInfo.ToTitleCase($key).Replace(' ', '').Trim()
                    if ($key -in @('BackupStart', 'BackupEnd'))
                    {
                        $value = [DateTime]::Parse($value.Trim())
                    }
                    elseif ($key -eq 'BackupType')
                    {
                        switch ($value.Trim())
                        {
                            '1 (Database)' {  $value = 'FULL' }
                            '2 (Transaction log)' {  $value = 'LOG' }
                            '5 (Differential database)' {  $value = 'DIFF' }
                        }
                    }
                    else
                    {
                        $value = $value.Trim()
                    }
                    $fileInfo.Add($key, $value)
                }
            }
        }
        if ($fileInfo.SqlBackupExitCode)
        {
            Write-Warning "SQL Backup Error: `n`n$(Get-RedgateSQLBackupError -ErrorNumber $fileInfo.SqlBackupExitCode )"
        }
        else
        {
            Write-Output $fileInfo
        }
    }
    END { }
}
# .\PSRedgate\public\Get-RedGateInstallationInfo.ps1
function Get-RedGateInstallationInfo
{
    <#
    .SYNOPSIS
    This cmdlet will return a hash with a list of installed redgate applications on this machine, as well as their locations

    .DESCRIPTION
    This cmdlet is used to locate cmdlets, to prevent relying on the path, as well as determining what versions are available on the machine.

    .EXAMPLE
    Get-RedGateInstallationInfo

    This will return a hash-table filled with the redgate applications installed on this machine.

    .EXAMPLE
    Get-RedGateInstallationInfo -ApplicationName 'SQL Prompt'

    This will return a hash-table containing with the redgate application 'SQL Prompt' installed on this machine.

    .NOTES
    This cmdlet is useful because it prevents us having to affect the user's path, while still making it quick to access
    the command line tool locations.
    #>

    [CmdletBinding()]
    param (
        [Parameter()]
        # The name of the application you want the information about
        [string] $ApplicationName,

        [Parameter()]
        # This will return only the latest version of an application
        [switch] $LatestVersion
    )
    BEGIN
    {
        $executables = @{
            'SQL Source Control'              = ''
            'SQL Data Generator'              = 'SQLDataGenerator.exe'
            'SSMS Integration Pack Framework' = ''
            'SQL Doc'                         = 'SQLDoc.exe'
            'SQL Test'                        = ''
            'SQL Compare'                     = 'SQLCompare.exe'
            'DLM Automation'                  = ''
            'SQL Dependency Tracker'          = ''
            'SQL Multi Script'                = 'SQLMultiScript.exe'
            'SQL Data Compare'                = 'SQLDataCompare.exe'
            'SSMS Integration Pack'           = ''
            'SQL Search'                      = ''
            'SQL Prompt'                      = ''
        }

        # loading private data from the module manifest
        $private:PrivateData = $MyInvocation.MyCommand.Module.PrivateData
        $installationInformation = $private:PrivateData['installationInformation']
    }
    PROCESS
    {
        try
        {
            if (-not($installationInformation))
            {
                $installationInformation = Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\*, HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* |
                    Select-Object DisplayName, DisplayVersion, Publisher, InstallDate, InstallLocation |
                    Select-Object *, @{Label = "InstallationDate"; Expression = {[datetime]::ParseExact($PSItem.InstallDate, "yyyyMMdd", $null).ToString('MM/dd/yyyy')}} | # casts install date to datetime
                    Select-Object * -ExcludeProperty 'InstallDate' | # remove integer date
                    Select-Object *, @{Label = "ApplicationName"; Expression = {$($PSItem.DisplayName -replace "\d+$", '').Trim()}} | # removes the version number from the application
                    Select-Object *, @{Label = "ExecutableName"; Expression = {$executables[$PSItem.ApplicationName]}} | # appends the exe name to the collection
                    Where-Object Publisher -Like 'Red Gate*'

            }
            $private:PrivateData['installationInformation'] = $installationInformation

            $result = $installationInformation | Where-Object ApplicationName -Like "*$ApplicationName*"

            if ($LatestVersion)
            {
                $result = $result | Group-Object ApplicationName |
                    ForEach-Object {
                    $PSItem.Group |
                        Sort-Object DisplayVersion |
                        Select-Object -Last 1
                }
            }
            Write-Output $result
        }
        catch
        {
            Write-Output  $PSItem.Exception | Format-List -Force
            break
        }
    }
    END
    {
    }
}
# .\PSRedgate\public\Get-RedgateMultiScriptConfigurationFileInfo.ps1
function Get-RedgateMultiScriptConfigurationFileInfo
{
    <#
    .SYNOPSIS
    This cmdlet allows you to retrieve information from a Multi Script configuration file, such as Saved Credentials, Distribution List, or Database Connections.

    .DESCRIPTION
    This cmdlet is useful for automating actions with Redgate's Multi Script. You often need the ability to pull out values for reuse.

    .EXAMPLE
    Get-RedgateMultiScriptConfigurationFileInfo -ReturnType 'SavedCredentials'

    This allows you to retrieve the list of saved connections from your existing Application.dat file. If you want to use existing credential info this is the easiest way to do it.
    This needs to exist because I can't replicate how Redgate encrypts these passwords and so if you want to use non-integrated auth, you have to set it up once. After that point, you
    can use this to retrieve an existing credential.
    This will default to Application.dat. You can use the -Path parameter to specify a different .dat file.

    Output =>
        version : 5
        type : database
        name : ExampleDB
        server : SQLEXAMPLE
        integratedSecurity : (True | False)
        username : (admin) # if not integrated
        savePassword : (True | False)
        password : [object]
        passwordValue : (qaasdfaswoqpweuqtoqirewu/+password+fyi/thisisfake==) # this is the encrypted version of the saved password.
        connectionTimeout : 15
        protocol : -1
        packetSize : 4096
        encrypted : False
        selected : True
        cserver : SQLEXAMPLE\EX1

    .EXAMPLE
    Get-RedgateMultiScriptConfigurationFileInfo -ReturnType 'DistributionLists'

    This will return the named distribution lists you're using in the application. This is so that you can overwrite the same distribution list with the Set-RedgateMultiScriptConfigurationFileInfo.
    This will default to Application.dat. You can use the -Path parameter to specify a different .dat file.

    Output =>
        version : 2
        type : databaseList
        name : Default Distribution List
        databases : databases
        guid : 1d141f74-4eb7-415d-b9b8-d3f92a7383ff


    .EXAMPLE
    Get-RedgateMultiScriptConfigurationFileInfo -ReturnType 'DatabaseConnections'

    This cmdlet allows you to inspect what servers and databases are in a Multi Script configuration file.
    This will default to Application.dat. You can use the -Path parameter to specify a different .dat file.

    Output =>
        server | name
        ---------- | --------------
        SQLEXAMPLE1 | WideWorldImporters
        SQLEXAMPLE1 | WideWorldImportersDW
        SQLEXAMPLE1 | master
        SQLEXAMPLE1 | model
        SQLEXAMPLE1 | msdb
        SQLEXAMPLE1 | tempdb

    .EXAMPLE
    Get-RedgateMultiScriptConfigurationFileInfo -FileLocation

    Returns the location on disk of the default Application.dat file to prevent us from having to do this anywhere we need to figure it out. If you pass in a path as well, it will just return that value to you.
    Which admittedly is kind of silly. So don't do that?

    .NOTES
    This functionality is originally inspired by Andy Levy (@andylevy) from feedback that I received from him. Thanks for the contribution.
    #>
    [CmdletBinding()]
    param(
        [Parameter()]
        # the location of the file that you would like to get information about, defaults to the standard location.
        [string] $Path,

        [Parameter()]
        <#
            Tells the cmdlet to return the location of the Application.dat file. I just wanted to centralize this logic. I realize this could be redundant.
            This is not present in the ReturnType parameter because we need the ability to find the correct file location, even if the file is missing or corrupt
            and it makes the code much cleaner to separate it out.
        #>
        [switch] $FileLocation,

        [Parameter()]
        # The type of information you are trying to retrieve from the config file.
        [ValidateSet('DatabaseConnections', 'DistributionLists', 'SavedCredentials')]
        [string] $ReturnType,

        [Parameter()]
        # tell the cmdlet to return things like the path even if it was unable to verify the file exists.
        [switch] $Force
    )
    BEGIN
    {
        try
        {
            $applicationInfo = Get-RedGateInstallationInfo -ApplicationName 'SQL Multi Script'

            if (-not($applicationInfo))
            {
                Write-Warning 'SQL Multi Script does not appear to be installed on this machine.'
                break
            }

            if (-not($Path))
            {
                Write-Verbose 'A path was not supplied, checking the default location for the Application.dat file.'
                $Path = "$($env:APPDATA)\Red Gate\$($applicationInfo.DisplayName)\Application.dat"
            }

            if ($FileLocation)
            {
                return $Path

            }

            if (-not(Test-Path $Path))
            {
                Write-Warning 'Unable to locate Application.dat file for SQL Multi Script. Please provide a valid location for the file.'
                break
            }

            [xml]$configData = Get-Content $Path
        }
        catch [System.Exception.InvalidCastToXmlDocument]
        {
            Write-Warning 'Unable to parse Application.dat file. Please verify that it is a valid xml file.'
            break
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break
        }
    }
    PROCESS
    {
        try
        {
            switch ($ReturnType)
            {
                'SavedCredentials'
                {
                    $attributes = @(
                        'version'
                        , 'type'
                        , 'name'
                        , 'server'
                        , 'integratedSecurity'
                        , 'username'
                        , 'savePassword'
                        , 'password'
                        , 'connectionTimeout'
                        , 'protocol'
                        , 'packetSize'
                        , 'encrypted'
                        , 'selected'
                        , 'cserver'
                    )
                    $result = $configData.multiScriptApplication.databaseLists.value.databases.value |
                        Select-Object $attributes -Unique |
                        Select-Object *, @{label = 'passwordValue'; expression = {$PSItem.password.'#text'}}
                }

                'DatabaseConnections'
                {
                    $result = $configData.multiScriptApplication.databaseLists.value.databases.value |
                        Select-Object server, name -Unique
                }
                'DistributionLists'
                {
                    $result = $configData.multiScriptApplication.databaseLists.value
                }
            }

            Write-Output $result
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break
        }
    }
    END
    {

    }
}

# .\PSRedgate\public\Get-RedgateSQLBackupError.ps1
function Get-RedgateSQLBackupError
{
    <#
        .SYNOPSIS
            This cmdlet will allow you to look up errors from Redgate's website for sql backup.

        .DESCRIPTION
            Using a web scraper, you can pull down the error codes and dump them into a hash table.
            This cmdlet will pull down the codes and store it in a local file.

        .EXAMPLE
            Get-RedgateSQLBackupError -ErrorNumber 999

            This will return the full text name and description of the error that was returned from Redgate's documentation.

        .EXAMPLE
            Get-RedgateSQLBackupError -RefreshErrorCodes

            Will update the local cache used by this command by scraping Redgate's SQL Backup website for their documentation. Useful if you think your codes are out-dated
   #>

    [CmdletBinding()]
    param (
        [Parameter()]
        # The error number reported by the application that you would like to retrieve the description for.
        [int] $ErrorNumber,

        [Parameter()]
        # The error number reported by the application that you would like to retrieve the description for.
        [ValidateRange(6, 9)]
        [int] $SQLBackupVersionNumber = 9,

        [Parameter()]
        # Will instruct the cmdlet to pull a fresh list of error codes from the redgate website.
        [switch] $RefreshErrorCodes
    )
    BEGIN
    {

        # There can be issues when pulling data over https if your network has rules about TLS1.1 vs TLS1.2
        # TODO determine if this is kosher and effective
        if ([Net.ServicePointManager]::SecurityProtocol -le [Net.SecurityProtocolType]::Tls12)
        {
            # It's not safe or good form to change environment settings. Storing current value to reset at the end of the call.
            Write-Verbose 'Current TLS protocol is set to a value lower than some allow. Temporarily using higher TLS protocol.'
            $currentSecurityProtocol = [Net.ServicePointManager]::SecurityProtocol
            [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        }

        $SQLBackupVersionDocumentationUrl = @{
            6 = 'https://documentation.red-gate.com/sbu6/errors-and-warnings/sql-backup-errors-500-5292'
            7 = 'https://documentation.red-gate.com/sbu7/errors-and-warnings/sql-backup-errors-500-5292'
            8 = 'https://documentation.red-gate.com/display/SBU8/SQL+Backup+errors+500+-+5292'
            9 = 'https://documentation.red-gate.com/sbu9/errors-and-warnings/sql-backup-errors-500-5292'
        }
        # loading private data from the module manifest
        $private:PrivateData = $MyInvocation.MyCommand.Module.PrivateData

        $errorList = $private:PrivateData['errorList']
        $dataLocation = "$($env:LOCALAPPDATA)\PSRedgate\data"
        $cacheFile = "$dataLocation\errorList.xml"

        # if we were unable to pull in the error list from private data, let's go ahead and make sure that we know where the data file goes.
        if (-not($errorList))
        {
            Write-Verbose 'No error list exists on file. Lets create a cache file to avoid too many web calls.'
            if (-not(Test-Path $dataLocation))
            {
                Write-Verbose 'We need to create a location on disk to store the error list cache.'
                New-Item -Path $dataLocation -ItemType Directory | Out-Null
            }

            # lets pull in our errors from a cache file if we can.
            Write-Verbose 'Loading error codes from cache file.'
            if (Test-Path $cacheFile)
            {
                $errorList = Import-CliXML -Path $cacheFile
            }
            else
            {
                Write-Verbose 'Unable to locate cache file.'
            }
        }
    }
    PROCESS
    {
        try
        {
            # this will pull new codes from redgate and overwrite the cached file.
            if ($RefreshErrorCodes -or -not($errorList))
            {
                Write-Verbose -Message "Pulling the latest values from Redgate SQL Backup Error Codes"
                $response = Invoke-WebRequest $SQLBackupVersionDocumentationUrl[$SQLBackupVersionNumber]

                Write-Verbose 'Data retrieved. Parsing backup error codes.'
                $entries = ( $response.ParsedHtml.getElementsByTagName("table") | Select-Object -First 1 ).rows
                $table = @()
                foreach ($entry in $entries)
                {
                    if ($entry.tagName -eq "tr")
                    {
                        $thisRow = @()
                        $cells = $entry.children
                        forEach ($cell in $cells)
                        {
                            if ($cell.tagName -imatch "t[dh]")
                            {
                                if ($cell.innerText -eq 'Error code')
                                {
                                    $thisRow += "`"$($($cell.innerText -replace '"', "'") -replace "Error code",'ErrorCode')`""
                                }
                                else
                                {
                                    $thisRow += "`"$($cell.innerText -replace '"', "'")`""
                                }
                            }
                        }
                        $table += $thisRow -join ","
                    }
                }
                $errorList = $table | ConvertFrom-Csv
            }
            Write-Output $errorList | Where-Object ErrorCode -EQ $ErrorNumber | Select-Object -ExpandProperty Description
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break
        }
    }
    END
    {
        #this will update the cache file if it hasn't been updated today
        if ((([System.IO.FileInfo]$cacheFile).LastWriteTime -le [DateTime]::Today) -or ($RefreshErrorCodes))
        {
            Write-Verbose "caching error codes to the file '$cacheFile'"
            $errorList | Export-Clixml -Path $cacheFile  -Encoding ASCII -Force
        }

        if ($currentSecurityProtocol)
        {
            Write-Verbose 'Setting the SecurityProtocol back to the previous value.'
            [Net.ServicePointManager]::SecurityProtocol = $currentSecurityProtocol
        }

        $private:PrivateData['errorList'] = $errorList
    }
}
# .\PSRedgate\public\Invoke-RedgateSQLCompare.ps1
function Invoke-RedgateSQLCompare
{
    <#
    .SYNOPSIS
        This is a powershell wrapper for interacting with the command line tools for SQL Compare. This will assist with making use of the underlying features of SQL Compare
        from inside your powershell scripts and cmdlets.

    .DESCRIPTION
        This cmdlet has a one-to-one parameter set that matches to the parameters passed to the SQLCompare.exe utility. The point of this cmdlet is to make it easier to
        use sql compare from powershell by promoting all of the parameters to first class citizens the way that powershell allows. The documentation for these parameters
        comes directly from Redgate's website and is fantastic. I highly recommend reading their descriptions and built in examples.

    .EXAMPLE
        Invoke-RedgateSQLCompare -Server1 SQLEXAMPLE1 -Database1 EXAMPLEDB -MakeSnapshot 'C:\temp\exampledb_snapshot.snp'

        This will create a schema snapshot of the database EXAMPLEDB on server SQLEXAMPLE1 and will save the snp file to the location specified.

    .EXAMPLE
        Invoke-RedgateSQLCompare -Snapshot1 'C:\temp\exampledb_snapshot.snp' -Server2 SQLEXAMPLE1 -Database2 EXAMPLEDB2 -Synchronize

        This will apply the schema snapshot taken from a database and synchronize the schema to EXAMPLEDB2 on server SQLEXAMPLE1

    .EXAMPLE
        Invoke-RedgateSQLCompare -Database1 'WidgetStaging' -Database2 'WidgetProduction' -Synchronize

        To compare the database structure of WidgetStaging with WidgetProduction, and deploy the databases by updating WidgetProduction:

    .EXAMPLE
        Invoke-RedgateSQLCompare -Project 'C:\SQLCompare\Projects\widgets.scp'

        Using an existing SQL Compare project from the command line
        This is useful, for example, as you can't choose or create a custom filter from the command line, and specifying complex object selections using a regular expression can be unwieldy.
        To use a project you have saved as "widgets.scp" from the command line:
            * When you use a project, all objects that were selected for comparison when you saved the project are automatically included.
            * When you use the command line, your project option selections are ignored and the defaults are used. Use /options to specify any additional options you want to use with a command line project.
            * For more information, see Options used in the command line.
            * If you want to include or exclude objects from an existing project, you must modify your selection using the user interface.
            * You can't use the /include and /exclude switches with /project.
            * Scheduling or automating a comparison or deployment You can use the Microsoft Windows Scheduled Task wizard to schedule a comparison by creating a script to run the comparison.

    .EXAMPLE
        Invoke-RedgateSQLCompare -Database1 'WidgetStaging' -Database2 'WidgetProduction' -Synchronize -MakeBackup

        Backing up the target database
        You can back up the target database before deployment. To back up WidgetProduction using the default settings.

    .EXAMPLE
        Invoke-RedgateSQLCompare -Database1 'WidgetStaging' -Database2 'WidgetProduction' -Synchronize -MakeBackup -BackupProvider 'SQB' -BackupType 'Differential' -BackupFolder 'C:\Backups' -BackupFile 'MyBackup.sqb' -BackupCompression 2 -BackupEncryption -BackupPassword $BackupPassword -BackupNumberOfThreads 3

        Backing up the target database
        To back up WidgetProduction using custom settings.
        When dealing with this many parameters, it would probably be easier to create a hash and splat it on the cmdlet.
        $params = @{
            Database1 = 'WidgetStaging'
            Database2 = 'WidgetProduction'
            Synchronize
            MakeBackup
            BackupProvider = 'SQB'
            BackupType = 'Differential'
            BackupFolder = 'C:\Backups'
            BackupFile = 'MyBackup.sqb'
            BackupCompression = 2
            BackupEncryption =
            BackupPassword = $BackupPassword
            BackupNumberOfThreads = 3
        }
        Invoke-RedgateSQLCompare @params


    .EXAMPLE
        Invoke-RedgateSQLCompare -Database 'WidgetProduction' -Makescripts 'C:\WidgetProductionScripts'

        Creating a scripts folder
        You can create a folder of object creation scripts representing the schema of a data source.
        To export WidgetProduction to a scripts folder:
        * If the folder does not already exist, it is created.
        * All the subfolders containing different object types in the schema are automatically created beneath the specified folder.
        * If the folder does exist, it must be empty or the export will fail.


    .EXAMPLE
        Invoke-RedgateSQLCompare -Scripts "C:\WidgetProductionScripts" -Database2 'WidgetStaging'

        Using a scripts folder or snapshot as a data source
            To compare the WidgetProduction scripts folder with the WidgetStaging database:


    .EXAMPLE
        Invoke-RedgateSQLCompare -Database1 'WidgetStaging' -Scripts2 'C:\WidgetProductionScripts" -Synchronize -Force

        To compare the WidgetStaging database with the WidgetProduction scripts folder and deploy the scripts to the folder:
            The /force switch specifies that any read-only files in the scripts folder that need to be edited during deployment will be made writable.
            If you do not include the /force switch and read-only files need to be modified, the deployment will fail and an error message will be displayed.


    .EXAMPLE
        Invoke-RedgateSQLCompare -SnapShot1 'C:\Snapshots\WidgetProd_1.snp' -Snapshot2 'C:\Snapshots\WidgetProd_2.snp'

        To compare two snapshots of WidgetStaging.


    .EXAMPLE
        Invoke-RedgateSQLCompare -Database1 'WidgetStaging' -Database2 'WidgetProduction' -ScriptFile 'C:\SQLScripts\Widgets.sql' -Force

        To output the deployment SQL script, for example for auditing purposes, and overwrite the file if it already exists.


    .EXAMPLE
        Invoke-RedgateSQLCompare -Backup1 'D:\MSSQL\BACKUP\WidgetDev_20080807_143235.sqb' -Database2 'WidgetLive'

        Comparing using a backup as a data source.


    .EXAMPLE
        Invoke-RedgateSQLCompare -Backup1 'D:\MSSQL\BACKUP\WidgetDev.bak' -BackupSet1 '2008-09-23 Full Backup' -Database2 'WidgetLive'

        To compare a backup of WidgetDev with WidgetLive:
        If you are comparing a backup set that contains multiple files, use the /backupset1 switch to specify the files which make up the first backup set,
        and use the /backupset2 switches to specify the files which make up the second:
        If the backup set switches are not specified, SQL Compare uses the latest backup set.
        To specify more than one backup file, separate the file names using semicolons -Backup1 "D:\WidgetDev_Full.bak";"D:\WidgetDev_Diff.bak"

    .NOTES
        This is still sort of raw, I'd like to sit down and map out all of the related parameters and create parameter sets out of them.
        I would also like to create a lot of examples of things you an do with this cmdlet.
        I'm standing on the shoulders of giants with this stuff, the sql compare engine is a really amazing piece of software engineering and in my experience is pretty rock solid.
        This cmdlet is just to make it easier to interact with it from powershell.
    #>
    [CmdletBinding(SupportsShouldProcess = $true)]
    param(
        [parameter()]
        [Alias('aow')]
        [ValidateSet('None' , 'Medium' , 'High')]
        # Specifies that SQL Compare won't run a deployment if there are any serious deployment warnings. If you don't specify this switch, SQL Compare will ignore warnings and run the deployment.
        [string]$AbortOnWarnings = 'None',

        [parameter()]
        #Attempts to activate SQL Compare.
        [switch]$activateSerial,

        [parameter()]
        # Runs a file containing an XML argument specification:
        [string]$Argfile,

        [parameter()]
        # When /assertidentical is specified, SQL Compare will return an exit code of 0 if the objects being compared are identical. If they aren't identical, it will return exit code 79.
        [string]$Assertidentical,

        [parameter()]
        [Alias('b1')]
        <#
        Specifies the backup to be used as the source.
        You must add all of the files making up the backup set you want to compare:
            sqlcompare /Backup1:D:\BACKUPS\WidgetStaging.bak /db2:WidgetStaging
        To specify more than one backup file, the file names are separated using semicolons:
            sqlcompare /Backup1:D:\BACKUPS\WidgetDev_Full.bak; D:\BACKUPS\WidgetDev_Diff.bak /db2:WidgetDev
        #>
        [string]$Backup1,

        [parameter()]
        [Alias('b2')]
        <#
        Specifies the backup to be used as the target.
        You must add all of the files making up the backup set you want to compare:
            sqlcompare /db1:WidgetStaging /Backup2:D:\BACKUPS\WidgetStaging.bak
        #>
        [string]$Backup2,

        [parameter()]
        [Alias('bc')]
        [ValidateRange(1, 3)]
        <#
        Compresses a backup using one of three compression levels.
        Arguments:
        1 Compression level 1 is the fastest compression, but results in larger backup files. On average, the backup process is 10% to 20% faster than when compression level 2 is used, and 20% to 33% fewer CPU cycles are used. Backup files are usually 5% to 9% larger than those produced by compression level 2. However, if a database contains frequently repeated values, compression level 1 can produce backup files that are smaller than if you used compression level 2 or 3. For example, this may occur for a database that contains the results of Microsoft SQL Profiler trace sessions.
        2 This compression level uses the zlib compression algorithm, and is a variation of compression level 3. On average, the backup process is 15% to 25% faster than when compression level 3 is used, and 12% to 14% fewer CPU cycles are used. Backup files are usually 4% to 6% larger.
        3 Compression level 3 uses the zlib compression algorithm. This compression level generates the smallest backup files in most cases, but it uses the most CPU cycles and takes the longest to complete.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupcompression:3
        #>
        [int]$BackupCompression,

        [parameter()]
        [Alias('be')]
        <#
        Encrypts a backup using 128-bit encryption.
        You can only encrypt Redgate (SQL Backup Pro) backups.
        If you encrypt a backup, you must specify a password using /BackupPassword.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupencryption /backuppassword:P@ssw0rd
        #>
        [switch]$BackupEncryption,

        [parameter()]
        [Alias('bf')]
        <#
        The file name to use when creating a backup.
        For Redgate backups, use the file extension .sqb. For native SQL Server backups, use .bak.
        sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupfile:WidgetProductionBackup.sqb
        #>
        [string]$BackupFile,

        [parameter()]
        [Alias('bd')]
        <#
        The folder to use for storing backups.
        If you don't use this switch, backups are stored in the folder specified in the SQL Backup options for the SQL Server instance.
        If you're not using SQL Backup, or no backup file locations have been set up, backups are stored in the SQL Server instance's default backup folder,
        for example: C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupfolder:C:\Backups
        #>
        [string]$BackupFolder,

        [parameter()]
        [Alias('bth')]
        [ValidateRange(1, 32)]
        <#
        Uses multiple threads to speed up the backup process. SQL Backup can use up to a maximum of 32 threads.
        We recommend you start with one thread fewer than the number of processors. For example, if you are using four processors, start with three threads.
        You can only use multiple threads with Redgate (SQL Backup Pro) backups.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupnumberofthreads:2
        #>
        [int]$BackupNumberOfThreads,

        [parameter()]
        [Alias('boe')]
        # Overwrites existing backup files of the same name when creating a backup.
        [switch]$BackupOverwriteExisting,

        [parameter()]
        [Alias('bt')]
        [ValidateSet('Full' , 'Differential' )]
        <#
        The type of backup to perform.
        Arguments:
            Full - Full backup
            Differential - Differential backup
        The default is Full.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backuptype:differential
        #>
        [string]$BackupType = 'Full',

        [parameter()]
        [Alias('bp')]
        # The password to use when encrypting a backup. sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupencryption /backuppassword:P@ssw0rd
        [securestring]$BackupPassword,

        [parameter()]
        [Alias('bpsw1')]
        # Specifies the password for the source backup. sqlcompare /Backup1:D:\BACKUPS\WidgetStaging.bak /BackupPasswords1:P@ssw0rd /db2:WidgetProduction
        [SecureString]$BackupPasswords1,

        [parameter()]
        [Alias('bpsw2')]
        # Specifies the password for the target backup. sqlcompare /db1:WidgetStaging /Backup2:D:\BACKUPS\WidgetProduction.bak /BackupPassword2:P@ssw0rd
        [SecureString]$BackupPasswords2,

        [parameter()]
        [Alias('bpr')]
        [ValidateSet('Native' , 'SQB' )]
        <#
        The format of the backup file to create when backing up the target database.
        Arguments:
            Native - Native SQL Server backup (.bak)
            SQB - SQL Backup Pro backup (.sqb)
        The default is native in SQL Compare 11.1.5 and later. On previous versions, the default was SQB.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup /backupprovider:native
        #>
        [string]$BackupProvider = 'Native',

        [parameter()]
        [Alias('bks1')]
        <#
        If you are comparing a backup set that contains multiple files, use the /BackupSet1 switch to specify the files which make up the source backup set,
        and use the /BackupSet2 switches to specify the files which make up the target:
            sqlcompare /Backup1:"D:\MSSQL\BACKUP\WidgetDev.bak" /BackupSet1:"2008-09-23 Full Backup" /db2:WidgetLive
        If the backup set switches aren't specified, SQL Compare uses the latest backup set.
        To specify more than one backup file, the file names are separated using semi-colons:
            sqlcompare /Backup1:D:\BACKUPS\WidgetDev_Full.bak; "D:\BACKUPS\WidgetDev_Diff.bak" /db2:WidgetDevlopment
        For encrypted backups that have been created using SQL Backup, use the /BackupPasswords1 and /BackupPasswords2 switches to specify the passwords; when there is more than one password, the passwords are separated using semi-colons.
        #>
        [string]$BackupSet1,

        [parameter()]
        [Alias('bks2')]
        <#
        Specifies which backup set to use for the target backup:
            sqlcompare /db1:WidgetProduction /BackupSet2:"2008-09-23 Full Backup"
        #>
        [string]$BackupSet2,

        [parameter()]
        [Alias('db1')]
        <#
        Specifies a database to use as the source:
            sqlcompare /Database1:WidgetStaging /Database2:WidgetProduction
        #>
        [string]$Database1,

        [parameter()]
        [Alias('db2')]
        # Specifies a database to use as the target.
        [string]$Database2,

        [parameter()]
        # This switch is case sensitive. Attempts to deactivate the application. An internet connection is required to deactivate the product.
        [switch]$deactivateSerial,

        [parameter()]
        <#
        Use this as the target data source to make a script that creates the source database schema. You can use this script with SQL Packager 8.
        For example, you want to package the schema of a database, WidgetStaging, so that when the package is run it will create a copy of the database schema.
            sqlcompare /Server1:MyServer\SQL2014 /Database1:WidgetStaging /empty2 /ScriptFile:"C:\Scripts\WidgetStagingSchema.sql"
        #>
        [switch]$empty2,

        [parameter()]
        [ValidateSet('Additional ', 'Assembly', 'AsymmetricKey', 'Certificate', 'Contract', 'DdlTrigger', 'Different ', 'EventNotification', 'ExtendedProperty', 'FullTextCatalog', 'FullTextStoplist', 'Function', 'Identical ', 'MessageType', 'Missing ', 'PartitionFunction', 'PartitionScheme', 'Queue', 'Role', 'Route', 'Rule', 'Schema', 'SearchPropertyList', 'Sequence', 'Service', 'ServiceBinding', 'Static data ', 'StoredProcedure', 'SymmetricKey', 'Synonym', 'Table', 'User', 'UserDefinedType', 'View', 'XmlSchemaCollection')]
        <#
        Excludes objects from the comparison. For example, to exclude objects that are identical in both the source and target:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /exclude:Identical
        To exclude an object type:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /exclude:table
        To exclude specific objects, use a regular expression:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /exclude:table:Widget*
        For more examples using regular expressions, see Selecting tables with unrelated names.
        If you want to set up complex rules to exclude objects (eg to exclude tables with a specific name and owner), use a filter instead.
        To exclude more than one object or object type, use multiple /exclude switches:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /exclude:table:WidgetReferences /exclude:view
        If an object is matched by both /include and /exclude, the /exclude rule takes priority and the object is excluded.
        You can't use /exclude with the /project switch.
            Additional - Objects that aren't in the source (eg /db1).
            Missing - Objects that aren't in the target (eg /db2).
            Different - Objects that are the source and the target, but are different.
            Identical - Objects that are identical in the source and the target.
            Static data - Static data in a source-controlled database or a scripts folder.
        To exclude object types, use:
            Assembly AsymmetricKey Certificate Contract DdlTrigger
            EventNotification ExtendedProperty FullTextCatalog FullTextStoplist
            Function MessageType PartitionFunction PartitionScheme Queue
            Role Route Rule Schema SearchPropertyList Sequence
            Service ServiceBinding StoredProcedure SymmetricKey Synonym
            Table User UserDefinedType View XmlSchemaCollection
        #>
        [string]$exclude,

        [parameter()]
        [Alias('ftr')]
        <#
        Specifies a custom filter to select objects for deployment.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /filter:MarketingViewsOnly.scpf
        You can set up a filter to include or exclude objects based on their type, name, and owner (schema) name.
        This is useful, for example, if you want to create complex selection rules without using regular expressions.
            Filters are set up in the user interface.
            Filters are saved with the extension .scpf
            filter can't be used with /Include or /Exclude.

            If you use /filter with /project, the filter you specify overrides any filter used in the project.
            For more information, see Using filters.
        #>
        [string]$Filter
        ,

        [parameter()]
        [Alias('f')]
        # Forces the overwriting of any output files that already exist. If this switch isn't used and a file of the same name already exists, the program will exit with the exit code indicating an IO error.
        [switch]$Force,

        [parameter()]
        [Alias('/?')]
        <#
        Displays the list of switches in the command line with basic descriptions.
        If /help is used with any switches except /verbose, /html, /out, /force or /outputwidth then those extra switches will be ignored;
        the help message will be printed and the process will end with exit code 0.
        #>
        [switch]$Help,

        [parameter()]
        # Outputs the help text as HTML. Must be used with the /help switch.
        [switch]$HTML,

        [parameter()]
        <#
        If SQL Compare encounters any high level errors when parsing a scripts folder, it will exit with an error code of 62.
        Use /ignoreParserErrors to force SQL Compare to continue without exiting.
        #>
        [switch]$IgnoreParserErrors,

        [parameter()]
        <#
        When you are creating a scripts folder using /makescripts, SQL Compare automatically detects the case sensitivity of the data source.
        Use /ignoreSourceCaseSensitivity to disable automatic detection of case sensitivity.
        #>
        [switch]$IgnoreSourceCaseSensitivity,

        [parameter()]
        [ValidateSet('Additional ', 'Assembly', 'AsymmetricKey', 'Certificate', 'Contract', 'DdlTrigger', 'Different ', 'EventNotification', 'ExtendedProperty', 'FullTextCatalog', 'FullTextStoplist', 'Function', 'Identical ', 'MessageType', 'Missing ', 'PartitionFunction', 'PartitionScheme', 'Queue', 'Role', 'Route', 'Rule', 'Schema', 'SearchPropertyList', 'Sequence', 'Service', 'ServiceBinding', 'Static data ', 'StoredProcedure', 'SymmetricKey', 'Synonym', 'Table', 'User', 'UserDefinedType', 'View', 'XmlSchemaCollection')]
        <#
        Includes objects in the comparison. For example, to include tables:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /include:table
        To include specific objects, use a regular expression:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /include:table:Widget*
        For more examples using regular expressions, see Selecting tables with unrelated names.
        If you want to set up complex rules to include objects (eg to include tables with a specific name and owner), use a filter instead.
        To include more than one object or object type, use multiple /include switches:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /include:table:WidgetReferences /include:view
        If an object is matched by both /include and /exclude, the /exclude rule takes priority and the object is excluded.
        You can't use /include with the /project switch.
            Additional - Objects that aren't in the source (eg /db1).
            Missing - Objects that aren't in the target (eg /db2).
            Different - Objects that are the source and the target, but are different.
            Identical - Objects that are identical in the source and the target.
            StaticData - Static data in a source-controlled database or a scripts folder. Can't be used with snapshot data sources.
        To include object types, use:
            Assembly AsymmetricKey Certificate Contract DdlTrigger EventNotification ExtendedProperty
            FullTextCatalog FullTextStoplist Function MessageType PartitionFunction PartitionScheme Queue
            Role Route Rule Schema SearchPropertyList Sequence Service ServiceBinding StoredProcedure SymmetricKey
            Synonym Table User UserDefinedType View XmlSchemaCollection
        #>
        [string]$include,

        [parameter()]
        [Alias('log')]
        <#
        Creates a log file with a specified minimum log level.
        Log files collect information about the application while you are using it. These files are useful to us if you have encountered a problem. For more information, see Logging and log files.

        Arguments:
        None - Disables logging
        Error - Reports serious and fatal errors
        Warning - Reports warning and error messages
        Verbose - Reports all messages in the log file

        The default is None.
        For example:
            sqlcompare /db1:WidgetStaging /makeScripts:"D:\ScriptsFolder" /logLevel:Verbose

        You must use /logLevel each time you want a log file to be created.
        #>
        [ValidateSet('None', 'Error', 'Warning', 'Verbose')]
        [string]$LogLevel = 'None',

        [parameter()]
        <#
        Backs up the target database using Redgate SQL Backup Pro or SQL Server native.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /sync /makebackup
        #>
        [switch]$MakeBackup,

        [parameter()]
        [Alias('mkscr')]
        <#
        Creates a scripts folder from the data source.
            sqlcompare /db1:WidgetStaging /makeScripts:"C:\Scripts Folders\Widget staging scripts"
        If the folder already exists an error will occur. To merge scripts into an existing scripts folder, compare them with that folder and use the /synchronize switch:
            sqlcompare /scr1:"C:\Scripts Folders\Widget dev scripts" /scr2:"C:\Scripts Folders\Widget staging scripts" /synchronize
        For more information, see Working with scripts folders.
        #>
        [string]$MakeScripts,

        [parameter()]
        [Alias('mksnap')]
        <#
        Creates a snapshot from the data source.
        sqlcompare /db1:WidgetStaging /makeSnapshot:"C:\Widget Snapshots\StagingSnapshot.snp"
        If the file already exists an error will occur, unless you have also used the /force switch.
        #>
        [string]$MakeSnapshot,

        [parameter()]
        [Alias('o')]
        <#
        Applies the project configuration options used during comparison or deployment:
        sqlcompare /db1:WidgetStaging /db2:WidgetProduction /options:Default,IgnoreWhiteSpace
        For a detailed list of these options see Options used in the command line.
        #>
        [string]$Options,

        [parameter()]
        <#
        Redirects console output to the specified file:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /out:C:\output file
        #>
        [string]$Out,

        [parameter()]
        [Alias('outpr')]
        <#
        Writes the settings used for the comparison to the specified SQL Compare project file:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /options:Default,IgnoreWhiteSpace /outputProject:"C:\WidgetProject.scp"
        This also generates a SQL Compare project file. These files end with a .scp extension. If the file already exists an error will occur, unless you have also used the /force switch.
        #>
        [string]$OutputProject,

        [parameter()]
        <#
        Forces the width of console output.
        This can be used to ensure that database object names etc aren't truncated, and that SQL script lines aren't wrapped or broken.
        This is particularly useful when redirecting output to a file as it allows you to overcome the limitations of the default console width of 80 characters.
        #>
        [string]$OutputWidth,

        [parameter()]
        [Alias('p1')]
        <#
        The password for the source database.
        You must also provide a username. If you don't specify a username and password combination, integrated security is used:
            sqlcompare /db1:WidgetStaging /userName1:User1 /password1:P@ssw0rd /db2:WidgetProduction /userName2:User2 /password2:Pa$$w0rd
        This switch is only used if the source is a database. If the source is a backup, use /backupPasswords1
        #>
        [securestring]$Password1,

        [parameter()]
        [Alias('p2')]
        #The password for the target database.
        [securestring]$Password2,

        [parameter()]
        [Alias('pr')]
        <#
        Uses a SQL Compare project (.scp) file for the comparison.
        To use a project you have saved as "widgets.scp" from the command line:
            sqlcompare /project:"C:\SQLCompare\Projects\Widgets.scp"
        When you use a project, all objects that were selected for comparison when you saved the project are automatically included.
        When you use the command line, your project option selections are ignored and the defaults are used. Use /options to specify any additional options you want to use with a command line project.
        For more information, see Options used in the command line. If you want to include or exclude objects from an existing project, you must modify your selection using the graphical user interface.
        You can't use the /include and /exclude switches with /project.
        The /project switch is useful, for example, as you can't specify a custom filter in the command line, and specifying complex object selections using a regular expression can be unwieldy.
        For more information on using projects, and what a project contains, see Working with projects.
        #>
        [string]$Project,

        [parameter()]
        [Alias('q')]
        #Quiet mode: no output.
        [string]$Quiet,

        [parameter()]
        [Alias('r')]
        <#
        Generates a report and writes it to the specified file.
        The type of report is defined by the /reportType switch. If the file already exists an error will occur, unless you have used the /force switch:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /report:"C:\reports\WidgetReport.html" /reportType:Simple
        #>
        [string]$Report,

        [parameter()]
        [Alias('rad')]
        # Includes all objects with differences in the reports, rather than all selected objects.
        [string]$ReportAllObjectsWithDifferences,

        [parameter()]
        [Alias('rt')]
        [ValidateSet('XML', 'Simple', 'Interactive', 'Excel')]
        <#
        Arguments:
        XML - Simple XML report
        Simple - Simple HTML report
        Interactive - Interactive HTML report
        Excel - Microsoft Excel spreadsheet

        This switch defines the file format of the report produced by the /Report switch. The default setting is XML.
        For example:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /report:"C:\reports\WidgetReport.html" /reportType:Simple
        For more information, see Exporting the comparison results.
        #>
        [string]$ReportType,

        [parameter()]
        [Alias('r1')]
        <#
        Specifies the source control revision of the source database. To specify a revision, the database must be linked to SQL Source Control.
        To specify the latest version, type: HEAD
        Specifying a revision other than HEAD is only supported with TFS, SVN and Vault.
        If you're using another source control system, we recommend checking the revision out to a local folder and using the /Scripts1 switch.
        The following example compares revision 3 of WidgetStaging with the latest revision of WidgetProduction:
            sqlcompare /db1:WidgetStaging /revision1:3 /db2:WidgetProduction /revision2:HEAD
        #>
        [string]$Revision1,

        [parameter()]
        [Alias('r2')]
        # Specifies the source control revision of the target database. To specify a revision, the database must be linked to SQL Source Control.
        [string]$Revision2,

        [parameter()]
        [Alias('sf')]
        <#
        Generates a SQL script to migrate the changes which can be executed at a later time. If the file already exists an error will occur, unless you use the /force switch:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /scriptFile:"C:\Scripts Folder\WidgetSyncScript.sql"
        Scriptfile can be used when the target ( /db2, /scr2, /sn2 ) is a database, a snapshot, or a scripts folder.
        If the target is a snapshot or a scripts folder, the generated script modifies a database with the schema represented by that snapshot or scripts folder.
        #>
        [string]$ScriptFile,

        [parameter()]
        [Alias('scr1')]
        <#
        Specifies the scripts folder to use as the source:
            sqlcompare /scripts1:"C:\Scripts Folder\WidgetStagingScript" /db2:WidgetProduction
        #>
        [string]$Scripts1,

        [parameter()]
        [Alias('scr2')]
        # Specifies the scripts folder to use as the target.
        [string]$Scripts2,

        [parameter()]
        [Alias('sfx')]
        <#
        The path to a text file containing XML that describes the location of a source control repository.
        The method you use to create this file depends on which version of SQL Source Control you are working with:
        If you are using SQL Source Control 4 or earlier
        In the SSMS Object Explorer, right-click a source-controlled database and click Properties.
        In the Database Properties dialog box, click Extended Properties:

        Copy the XML fragment from the SQLSourceControl Scripts Location extended property.
        Create a new text file and paste the XML fragment into it.
        Save the file.
        If you are using SQL Source Control 5.4 or later
        In the SQL Source Control Setup tab for a source-controlled database, click on the Show link next to Under the hood
        Copy the XML fragment from the SQL Compare XML fragment block to the clipboard by clicking the Copy button:

        Create a new text file and paste the XML fragment into it.
        Save the file.
        #>
        [string]$ScriptsFolderXML,

        [parameter()]
        [Alias('s1', 'SourceServer')]
        <#
        Specifies the server on which the source (/db1:) database is located. If an explicit path isn't specified, it defaults to Local.
            sqlcompare /server1:Widget_Server\SQL2008 /db1:WidgetStaging /db2:WidgetProduction
        #>
        [string]$Server1,

        [parameter()]
        [Alias('s2', 'TargetServer')]
        # Specifies the server on which the target (/db2:) database is located. If an explicit path isn't specified, it defaults to Local.
        [string]$Server2,

        [parameter()]
        [Alias('warn')]
        <#
        Displays any warnings that apply to the deployment. For more information on warnings in SQL Compare, see Warnings.
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /showWarnings
        #>
        [switch]$ShowWarnings,

        [parameter()]
        [Alias('sn1')]
        <#
        Specifies the snapshot to use as the source:
            sqlcompare /snapshot1:"C:\Snapshots\WidgetStagingSnapshot.snp" /db2:WidgetProduction
        #>
        [string]$Snapshot1,

        [parameter()]
        [Alias('sn2')]
        <#
        Specifies the snapshot to use as the target:
            sqlcompare /db1:WidgetStaging /snapshot2:"C:\Snapshots\WidgetProductionSnapshot.snp"
        #>
        [string]$Snapshot2,

        [parameter()]
        <#
        Specifies a folder of source-controlled scripts to use as the source.
        If you use this switch, you must also specify /scriptsfolderxml.
        If you want to use a specific revision of the database, you can also specify /revision1.
            sqlcompare /sourcecontrol1 /revision1:100 /sfx:"C:\Files\scripts.txt" /db2:WidgetProduction
        #>
        [switch]$Sourcecontrol1,

        [parameter()]
        <#
        Specifies a folder of source-controlled scripts to use as the target.
        If you use this switch, you must also specify /scriptsfolderxml.
        If you want to use a specific revision of the database, you can also specify /revision2.
            sqlcompare db1:WidgetStaging /sourcecontrol2 /revision2:100 /sfx:"C:\Files\scripts.txt"
        #>
        [switch]$Sourcecontrol2,

        [parameter()]
        [Alias('sync', 'synchronise')]
        <#
        Synchronizes (deploys) the databases after comparison.
        The target (for example, /db2) is modified; the source (for example, /db1) isn't modified:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /synchronize
        #>
        [switch]$Synchronize,

        [parameter()]
        [Alias('senc')]
        [ValidateSet('UTF8', 'UTF8WithPreamble', 'Unicode', 'ASCII')]
        <#
        Arguments:
        UTF8 - UTF-8 encoding, without preamble
        UTF8WithPreamble - UTF-8 encoding, with 3-byte preamble
        Unicode - UTF-16 encoding
        ASCII - ASCII encoding

        Used with /scriptFile. Specifies the character encoding used when writing the SQL script file. The default is UTF8.
        For example:
            sqlcompare /db1:WidgetStaging /db2:WidgetProduction /scriptFile:"C:\Scripts Folder\WidgetSyncScript.sql" /syncScriptEncoding:ASCII
        #>
        [string]$SyncScriptEncoding,

        [parameter()]
        [Alias('til')]
        [ValidateSet('READ UNCOMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SNAPSHOT', 'SERIALIZABLE')]
        <#
        Specifies the transaction isolation level to set in the deployment script. For information about transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (MSDN).
        Arguments:
            READ UNCOMITTED
            READ COMMITTED
            REPEATABLE READ
            SNAPSHOT
            SERIALIZABLE
        #>
        [string]$TransactionIsolationLevel,

        [parameter()]
        [Alias('u1')]
        <#
        The username for the source database.
        If no username is specified, integrated security is used.
            sqlcompare /db1:WidgetStaging /userName1:User1 /password1:P@ssw0rd /db2:WidgetProduction /userName2:User2 /password2:Pa$$w0rd
        #>
        [string]$UserName1,

        [parameter()]
        [Alias('u2')]
        <#
        The username for the target database.
        If no username is specified, integrated security is used.
        #>
        [string]$UserName2,

        [parameter()]
        [Alias('vu1')]
        <#
        Specifies the username for the source control server linked to the source database.
            sqlcompare /db1:WidgetStaging /v1:3 /versionUserName1:User1 /vp1:P@ssw0rd /db2:WidgetProduction /v2:HEAD /versionUserName2:User2 /vp2:Pa$$w0rd
        If you have a username saved in SQL Source Control, you don't need to specify it in the command line.
        #>
        [string]$VersionUserName1,

        [parameter()]
        [Alias('vu2')]
        # Specifies the username for the source control server linked to the target database.
        [string]$VersionUserName2,

        [parameter()]
        [Alias('vp1')]
        <#
        Specifies the password for the source control server linked to the source database.
            sqlcompare /db1:WidgetStaging /v1:3 /vu1:User1 /versionpassword1:P@ssw0rd /db2:WidgetProduction /v2:HEAD /vu2:User2 /versionpassword2:Pa$$w0rd
        If you have a password saved in SQL Source Control, you don't need to specify it in the command line.
        #>
        [securestring]$VersionPassword1,

        [parameter()]
        [Alias('vp2')]
        # Specifies the password for the source control server linked to the target database.
        [securestring]$VersionPassword2,

        [parameter()]
        # This will tell the cmdlet to execute the command instead of printing out the command
        [switch] $Execute
    )
    BEGIN
    {
    }
    PROCESS
    {
        try
        {
            # These are the command line options that are not used as options and are not needed below.
            # If you add parameters and don't want them to pass through as options, put them in this blacklist.
            $configuration = @('Execute', 'Verbose')
            $params = [ordered]@{}
            $arguments = ''

            #go get the information about the installation information
            Write-Verbose 'Getting installation info.'
            $installationInfo = Get-RedGateInstallationInfo -ApplicationName 'SQL Compare' -LatestVersion

            if (-not($installationInfo))
            {
                Write-Warning 'Unable to locate an installation of Redgate SQL Compare on this machine. Please ensure that the application is installed on this machine.'
                break
            }

            #this will give you the exec location
            $cmdPath = (Join-Path $installationInfo.InstallLocation $installationInfo.ExecutableName)

            $PSBoundParameters.GetEnumerator()| ForEach-Object {
                if ($PSItem.Key -notin $configuration)
                {
                    $params.Add($PSItem.Key, $PSItem.Value)
                }
            }

            #Build the with string so that it can be added to the command
            foreach ($param in $params.GetEnumerator())
            {
                if ($param.Value)
                {
                    $paramType = $param.Value.GetType().Name
                    $paramKey = $param.Key.ToString().ToLower()
                    $paramValue = $param.Value.ToString().ToLower()

                    if ($paramType -eq 'SwitchParameter')
                    {
                        $arguments += "/$paramKey "
                    }
                    elseif ($paramType -eq 'string')
                    {
                        $arguments += "/$($paramKey):`"$paramValue`" "
                    }
                    else
                    {
                        $arguments += "/$($paramKey):$paramValue "
                    }
                }
            }

            if ($PSCmdlet.ShouldProcess($installationInfo.ExecutableName, $arguments))
            {
                Start-Process -FilePath $cmdPath -ArgumentList $arguments -NoNewWindow
            }
        }
        catch
        {
            Write-Output $PSItem.Exception.Message
            break;
        }
    }
    END
    {
    }
}
# .\PSRedgate\public\Restore-RedgateDatabase.ps1
function Restore-RedgateDatabase
{
    <#
    .SYNOPSIS
        This cmdlet is a wrapper for the stored procedure master..sqlbackup. Instead of trying to handle remoting and talking to the command line tool, this cmdlet will
        allow you to simply pass in parameters to execute restores on your servers.

    .DESCRIPTION
        Using this cmdlet, you can perform all of the normal operations that you would do using the redgate sql backup ui, or would write manually using SSMS.

    .EXAMPLE
        An example

    .NOTES
        General notes
    #>


    [CmdletBinding(SupportsShouldProcess, DefaultParameterSetName = 'Default')]
    Param (
        [Parameter(Mandatory)]
        # The name of the database you want to backup.
        [string] $TargetSQLServerName,

        [Parameter()]
        # The name of the server containing the database you want to backup. Only needed if you are renaming the database and need to find out the file names.
        [string] $SourceSQLServerName,

        [Parameter(Mandatory)]
        # The location that you want this file backed up.
        [string] $DatabaseName,

        [Parameter(Mandatory)]
        [ValidateSet('LATEST_FULL', 'LATEST_DIFF', 'LATEST_ALL', 'LOG')]
        # The location that you want this file backed up.
        [string] $Type,

        [Parameter()]
        # This variable should be passed when you want to rename the database
        [string] $RestoreAs,

        [Parameter(Mandatory, ParameterSetName = 'FullPath')]
        # The file you would like to use in the restore command
        [string[]] $Disk,

        [Parameter(Mandatory, ParameterSetName = 'RelativePath')]
        # The location that you want these restores to be pulled from
        [string] $FileLocation,

        [parameter()]
        # If the backup is encrypted, pass a credential object with the password needed to decrypt the file.
        [System.Management.Automation.PSCredential] $DecryptionCredential,

        [parameter()]
        # If you would like to execute this as a different user and not use integrated auth, pass in a credential object to run as a different user.
        [System.Management.Automation.PSCredential] $Credential,

        [parameter()]
        # This flag indicates that the file is encrypted and we should pass the password to the cmdlet. This requires all files passed in the pipeline to be encrypted.
        [switch] $Encrypted,

        [Parameter()]
        # This parameter is needed if there is no way to determine what the logical name of a database is and you want to use $RestoreAs. This is the logical name needed to move the Data file.
        [string] $DatabaseDataFileLogicalName,

        [Parameter()]
        # This parameter is needed if there is no way to determine what the logical name of a database is and you want to use $RestoreAs. This is the logical name needed to move the Log file.
        [string] $DatabaseLogFileLogicalName,

        [Parameter()]
        # This is the location where you would like to put an undo file if you are going to create one.
        [string] $StandbyLocation,

        [Parameter()]
        [ValidateSet('NO_INFOMSGS' , 'ALL_ERRORMSGS' , 'TABLOCK' , 'PHYSICAL_ONLY' , 'DATA_PURITY' , 'EXTENDED_LOGICAL_CHECKS' , 'VERBOSE' )]
        #Runs a database integrity check (DBCC CHECKDB) on the database once the restore is complete. This checks the logical and physical integrity of all the objects in the specified database. CHECKDB cannot be used in conjunction with NORECOVERY.
        [string] $CHECKDB ,

        [Parameter()]
        #By default, if the backup process included WITH CHECKSUM the backup checksum and any page checksums are validated on restore. If the backup does not include a backup checksum, any page checksums will not be validated.
        [switch] $CHECKSUM ,

        [Parameter()]
        #Specify NO_CHECKSUM to disable default validation of checksums. If you specify CHECKSUM, the backup checksum and any page checksums will be validated as by default, but if the backup does not include a backup checksum, an error is returned.
        [switch] $NO_CHECKSUM ,

        [Parameter()]
        #specifies that the RESTORE process should continue after an error is encountered, restoring what it can. This is the default behavior for RESTORE VERIFYONLY (see VERIFY in The BACKUP command). The RESTORE VERIFYONLY process then reports all errors it has encountered.
        [switch] $CONTINUE_AFTER_ERROR ,

        [Parameter()]
        #specifies that the RESTORE process should stop if an error is encountered. This is the default behavior for RESTORE.
        [switch] $STOP_ON_ERROR,

        [Parameter()]
        #Kills any existing connections to the database before starting the restore. Restoring to an existing database will fail if there are any connections to the database.
        [switch] $DISCONNECT_EXISTING ,

        [Parameter()]
        [ValidateRange(1, 30000)]
        #Specifies a minimum age, in seconds, for transaction log backups. Only backups older than the specified age will be restored. This is useful if you are log shipping to maintain a standby database and want to delay restores to that database, for example, to help protect against corrupt or erroneous data.
        [int] $DELAY,

        [Parameter()]
        [ValidateRange(1, 120)]
        # specifies the time interval between retries, in seconds, following a failed data-transfer operation (reading or moving a backup file). If you omit this keyword, the default value of 30 seconds is used.
        [int] $DISKRETRYINTERVAL = 30,

        [Parameter()]
        [ValidateRange(1, 50)]
        # specifies the maximum number of times to retry a failed data-transfer operation (reading or moving a backup file). If you omit this keyword, the default value of 10 is used.
        [int] $DISKRETRYCOUNT = 10,

        [Parameter()]
        #Drops the database after the restore process (and database integrity check if used in conjunction with CHECKDB). The restored database is removed from the SQL Server instance regardless of whether any errors or warnings were returned.
        [switch] $DROPDB ,

        [Parameter()]
        #Drops the database if the restore completed successfully. When used in conjunction with CHECKDB, drops the database if the restore completed successfully and the database integrity check completed without errors or warnings.
        [switch] $DROPDBIFSUCCESSFUL ,

        [Parameter()]
        #Specifies the number of existing SQL Backup backups to be deleted from the MOVETO folder. This is useful for managing the number of backups in the MOVETO folder when log shipping. Number is days, number followed by h is hours, number followed by b is number to keep.
        [string] $ERASEFILES,

        [Parameter()]
        #Manages deletion of existing SQL Backup backups from remote MOVETO folders. This is useful for managing the number of files in the MOVETO folder when log shipping.
        [int] $ERASEFILES_REMOTE ,

        [Parameter()]
        #Manages deletion of existing SQL Backup backups from the DISK location. If multiple DISK locations are specified, the setting is applied to each folder. The backup files are deleted only if the backup process completes successfully.
        [int] $ERASEFILES_PRIMARY,

        [Parameter()]
        #Manages deletion of existing SQL Backup backups from the MOVETO folder.
        [int] $ERASEFILES_SECONDARY,

        [Parameter()]
        #Use in conjunction with ERASEFILES. Specifies whether backup files are to be deleted from the MOVETO folder. Specify the sum of the values that correspond to the options you require. 1 Delete backup files in the MOVETO folder if they are older than the number of days or hours specified in ERASEFILES. 2 Do not delete backup files in the MOVETO folder that are older than the number of days or hours specified in ERASEFILES if they have the ARCHIVE flag set.
        [int] $FILEOPTIONS ,

        [Parameter()]
        #Specifies that Change Data Capture settings are to be retained when a database or log is restored to another server. This option cannot be included with NORECOVERY.
        [switch] $KEEP_CDC ,

        [Parameter()]
        #This option is for use when log shipping is used in conjunction with replication. Specifies that replication settings are to be retained when a database or log is restored to a standby server. This option cannot be included with NORECOVERY.
        [switch] $KEEP_REPLICATION ,

        [Parameter()]
        #Specifies that a log file should only be created if SQL Backup Pro encounters an error during the restore process, or the restore completes successfully but with warnings. Use this option if you want to restrict the number of log files created by your restore processes, but maintain log information whenever warnings or errors occur. This argument controls the creation of log files on disk only;
        [switch] $LOG_ONERROR ,

        [Parameter()]
        #Specifies that a log file should only be created if SQL Backup Pro encounters an error during the restore process. Use this option if you want to restrict the number of log files created by your restore processes, but maintain log information whenever errors occur. This argument controls the creation of log files on disk only;
        [switch] $LOG_ONERRORONLY ,

        [Parameter()]
        #Specifies that a copy of the log file is to be saved.
        [string] $LOGTO ,

        [Parameter()]
        #Specifies that the outcome of the restore operation is emailed to one or more users; the email includes the contents of the log file.
        [string] $MAILTO,

        [Parameter()]
        #Specifies that SQL Backup Pro should not include the contents of the log file in the email. An email will still be sent to notify the specified recipients of success and/or failure, depending on which MAILTO parameter has been specified.
        [switch] $MAILTO_NOLOG ,

        [Parameter()]
        #Specifies that that the outcome of the restore operation is emailed to one or more users if SQL Backup Pro encounters an error during the restore process or the restore process completes successfully but with warnings. The email includes the contents of the log file.
        [string] $MAILTO_ONERROR ,

        [Parameter()]
        #Specifies that that the outcome of the restore operation is emailed to one or more users if SQL Backup Pro encounters an error during the restore process.
        [string] $MAILTO_ONERRORONLY,

        [Parameter()]
        #Specifies the data files should be restored to the specified location using the operating system file names defined in the backup file.
        [string] ${MOVE-DATAFILES-TO},

        [Parameter()]
        #Specifies that filestreams should be restored to the specified location. The specified location must exist before the restore, otherwise the restore will fail. If the database contains multiple filestreams, each filestream will be restored to a separate subfolder.
        [string] ${MOVE-FILESTREAMS-TO},

        [Parameter()]
        #Only available with SQL Server 2005. Specifies that full text catalogs should be restored to the specified location. If the database contains multiple full text catalogs, each full text catalog will be restored to a separate subfolder.
        [string] ${MOVE-FULLTEXTCATALOGS-TO},

        [Parameter()]
        #Specifies the log files should be restored to a new location with the operating system file names specified in the backup file.
        [string] ${MOVE-LOGFILES-TO},

        [Parameter()]
        #Specifies that the backup files should be moved to another folder when the restore process completes. If the folder you specify does not exist, it will be created.
        [string] $MOVETO,

        [Parameter()]
        #Prevents a log file from being created for the restore process, even if errors or warnings are generated. You may want to use this option if you are concerned about generating a large number of log files, and are certain that you will not need to review the details of errors or warnings (for example, because it's possible to run the process again without needing to know why it failed).
        [switch] $NOLOG ,

        [Parameter()]
        #Specifies that once the restore has completed, the database should be checked for orphaned users. Database user names are considered to be orphaned if they do not have a corresponding login defined on the SQL Server instance.
        [switch] $ORPHAN_CHECK ,

        [Parameter()]
        #Specifies the password to be used with encrypted backup files.
        [SecureString] $PASSWORD,

        [Parameter()]
        #Specifies the password file to be used with encrypted backup files.
        [SecureString] $PASSWORDFILE,

        [Parameter()]
        #Specifies that the database should be restored, even if another database of that name already exists. The existing database will be deleted. REPLACE is required to prevent a database of a different name being overwritten by accident. REPLACE is not required to overwrite a database which matches the name recorded in the backup.
        [switch] $REPLACE ,

        [Parameter()]
        #Specifies that access to the restored database is to be limited to members of the db_owner, dbcreator or sysadmin roles. Return the database to multi-user or single-user mode using your SQL Server application.
        [switch] $RESTRICTED_USER ,

        [Parameter()]
        #Specifies that the results returned by the RESTORE command should be limited to just one result set. This may be useful if you want to manipulate results using a Transact-SQL script. Such scripts can only manipulate results when a single result set is returned. The RESTORE command will return two result sets by default in most cases, unless you specify the SINGLERESULTSET keyword.
        [switch] $SINGLERESULTSET ,

        [Parameter()]
        #Specifies a standby file that allows the recovery effects to be undone. The STANDBY option is allowed for offline restore (including partial restore). The option is disallowed for online restore.
        [string] $STANDBY,

        [Parameter()]
        #Specifies a point in time to which a transaction log backup should be restored. The database will be recovered up to the last transaction commit that occurred at or before the specified time.
        [string] $STOPAT,

        [Parameter()]
        #Specifies that incomplete transactions are to be rolled back. Recovery is completed and the database is in a usable state. Further differential backups and transaction log backups cannot be restored.
        [switch] $RECOVERY ,

        [Parameter()]
        #Specifies that incomplete transactions are not to be rolled back on restore. The database cannot be used but differential backups and transaction log backups can be restored.
        [switch] $NORECOVERY ,

        [Parameter()]
        #This will specify that the database should be in standby, and will automatically locate the correct standby location
        [switch] $READONLY ,

        [Parameter()]
        #This will specify that the files should be moved to a specific drive, and will automatically locate the correct drive locations.
        [switch] $DEFAULT_LOCATIONS ,

        [Parameter()]
        [ValidateRange(0, 6)]
        #Sets the SQL Backup Pro thread priority when the backup or restore process is run. Valid values are 0 to 6, and correspond to the following priorities:
        [int] $THREADPRIORITY
    )
    BEGIN { }
    PROCESS
    {
        try
        {
            <#
                This command is based off of parameters that are passed in, this is a black list of parameters that the redgate
                command won't need and shouldn't be included in the automatic variable list rollup.
            #>
            $configuration = @(
                'TargetSQLServerName'
                'SourceSQLServerName'
                'Type'
                'RestoreAs'
                'DatabaseName'
                'Disk'
                'Encrypted'
                'Credential'
                'DecryptionCredential'
                'FileLocation'
                'DatabaseDataFileLogicalName'
                'StandbyLocation'
                'DatabaseLogFileLogicalName'
                'READONLY'
                'DEFAULT_LOCATIONS'
                'WhatIf'
                'Verbose'
            )

            $options = [ordered]@{}

            $defaultTargetLocations = Get-SQLServerDefaultFileLocation -SQLServerName $TargetSQLServerName

            if (($STANDBY -or $READONLY) -and -not($StandbyLocation))
            {
                <#
                    Okay, so figuring out where the UNDO files should go kind of sucks, I'm making some assumptions here about where to put these files.
                    I'm going to default to putting it into the "Backup" directory in the installation directory. Which I'm assuming is where you put master.
                    I'm open to suggestions on different ways to do this.
                #>
                $temp = Get-SQLServerDatabaseFile -SQLServerName $TargetSQLServerName -DatabaseName master | Where-Object DatabaseFileType -eq 'Data'
                $tempLocation = $temp.DatabaseFileLocation.TrimEnd("\DATA\$($temp.DatabaseFileName)")
                $StandbyLocation = "$tempLocation\Backup"

            }

            <#
                If $Disk is not passed, file location will allow you pass in a root directory.
                The assumption here is that you store your files in a location like this using the redgate AUTO naming.
                - $FileLocation\
                    - FULL
                    - DIFF
                    - LOG

                This will allow you to simply pass a root directory and based on your $Type variable, it will append on
                appropriate wildcard locations to make the restore work.
            #>
            if (!$Disk)
            {
                if ($Type -eq 'LATEST_ALL')
                {
                    $TypeDirectories = @('FULL', 'DIFF', 'LOG');
                }
                elseif ($Type -eq 'LATEST_FULL')
                {
                    $TypeDirectories = @('FULL');
                }
                elseif ($Type -eq 'LATEST_DIFF')
                {
                    $TypeDirectories = @('FULL', 'DIFF');
                }
                elseif ($Type -eq 'LOG')
                {
                    $TypeDirectories = @('LOG');
                }

                if ($Type -eq 'LOG')
                {
                    $Disks = "DISK = ''$fileLocation\LOG_$($DatabaseName)_*.sqb''";
                }
                else
                {
                    $Disks = -join ($TypeDirectories | ForEach-Object {"DISK = ''$fileLocation\$DatabaseName\$PSItem\*.sqb'', "})
                    $Disks = $Disks.Trim().Substring(0, $Disks.Length - 2)
                    $Disks += " $Type"
                }
            }
            else
            {

                <#
                    To my knowledge, there is no way to tell the file is encrypted ahead of time. I include _ENCRYPTED at the
                    end of my files so that this command can tell if it needs to supply a password dynamically without having
                    to know ahead of time. The command used to just ignore a password if it didn't need one, but will now
                    error out and so you have to be sure not to supply one if it's not needed. For the people that don't use
                    my convention, you just need to pass the -Encrypted flag
                #>
                $Encrypted = ($Disk.Split('_')[-1] -eq 'ENCRYPTED.sqb' -or $Encrypted)

                if ($Encrypted -and -not($DecryptionCredential) -and -not($PASSWORD -or $PASSWORDFILE))
                {
                    $DecryptionCredential = (Get-Credential -UserName 'SQL Backup' -Message 'Enter password to decrypt backup file.')
                }

                if (-not($Encrypted))
                {
                    #if this is not encrypted, we want to add it to the configuration array. This will strip it out of the command
                    $configuration += 'PASSWORD'
                    $configuration += 'PASSWORDFILE'
                }

                $Disks = "DISK = ''$Disk''"
            }

            $backupType = '';
            switch ($Type)
            {
                'LATEST_FULL' { $backupType = 'DATABASE'}
                'LATEST_DIFF' { $backupType = 'DATABASE' }
                'LATEST_ALL' { $backupType = 'DATABASE' }
                'LOG' { $backupType = 'LOG'}
            }

            # This handles special situations for custom parameters.
            $PSBoundParameters.GetEnumerator()| ForEach-Object {
                # This is the default route. All other instructions will be handled differently
                if ($PSItem.Key -notin $configuration)
                {
                    $options.Add($PSItem.Key, $PSItem.Value)
                }

                # This will create an undo file so that you can put it into read-only standby mode.
                if ($PSItem.Key -eq 'READONLY')
                {
                    $options.Add('STANDBY', "$($StandbyLocation)\UNDO_$DatabaseName.dat")
                }

                if ($PSItem.Key -eq 'DEFAULT_LOCATIONS' -and -not($RestoreAs))
                {
                    $options.Add('MOVE-DATAFILES-TO', $defaultTargetLocations.DefaultData)
                    $options.Add('MOVE-LOGFILES-TO', $defaultTargetLocations.DefaultLog)
                }

                # this is kind of involved, but super useful. It lets you rename the database when you restore it and figures out a lot of stuff for you.
                if ($PSItem.Key -eq 'RestoreAs' -and -not([string]::IsNullOrEmpty($PSItem.Value)))
                {
                    if (-not($SourceSQLServerName))
                    {
                        $SourceSQLServerName = $TargetSQLServerName
                    }
                    $databaseFiles = Get-SQLServerDatabaseFile -SQLServerName $SourceSQLServerName -DatabaseName $DatabaseName
                    if (-not($databaseFiles))
                    {
                        Write-Verbose "Couldn't find file info for $DatabaseName on $SourceSQLServerName. Trying to locate the info on the target $TargetSQLServerName"
                        $databaseFiles = Get-SQLServerDatabaseFile -SQLServerName $TargetSQLServerName -DatabaseName $DatabaseName
                    }

                    Write-Verbose "Finding the logical and physical file(s) names for the data so that we can rename the database."
                    $dataFiles = $databaseFiles | Where-Object DatabaseFileType -eq 'Data'
                    if ($dataFiles)
                    {
                        Write-Verbose 'Found the files on the source server. Using that to rename and move the database.'
                        foreach ($file in $DataFiles)
                        {
                            $options.Add("MOVE-''$($file.DatabaseLogicalName)''-TO", "$($defaultTargetLocations.DefaultData)\$($file.DatabaseFileName -replace $DatabaseName, $RestoreAs)")
                        }
                    }
                    elseif ($DatabaseDataFileLogicalName)
                    {
                        Write-Verbose "Couldn't find the database on the source server. Using the name provided to move the Logical files."
                        $options.Add("MOVE-''$DatabaseDataFileLogicalName''-TO", "$($defaultTargetLocations.DefaultData)\$($file.DatabaseFileName -replace $DatabaseName, $RestoreAs)")
                    }
                    else
                    {
                        Write-Verbose "Couldn't find the database on the source server. I'm going to have to guess what the database files was named. PROTIP: this might not work."
                        $options.Add("MOVE-''$DatabaseName''-TO", "$($defaultTargetLocations.DefaultData)\$RestoreAs.mdf")
                    }

                    Write-Verbose "Finding the logical and physical file(s) names for the log so that we can rename the database."
                    $logFiles = $databaseFiles | Where-Object DatabaseFileType -eq 'Log'
                    if ($logFiles)
                    {
                        Write-Verbose 'Found the files on the source server. Using that to rename and move the database.'
                        # You really shouldn't have multiples here. I"m about to put them back on the same drive. - https://www.brentozar.com/blitz/multiple-log-files-same-drive/
                        foreach ($file in $logFiles)
                        {
                            $options.Add("MOVE-''$($file.DatabaseLogicalName)''-TO", "$($defaultTargetLocations.DefaultData)\$($file.DatabaseFileName -replace $DatabaseName, $RestoreAs)")
                        }
                    }
                    elseif ($DatabaseLogFileLogicalName)
                    {
                        Write-Verbose "Couldn't find the database on the source server. Using the name provided to move the Logical files."
                        $options.Add("MOVE-''$DatabaseLogFileLogicalName''-TO", "$($defaultTargetLocations.DefaultData)\$($file.DatabaseFileName -replace $DatabaseName, $RestoreAs)")
                    }
                    else
                    {
                        Write-Verbose "Couldn't find the database on the source server. I'm going to have to guess what the database files was named. PROTIP: this might not work."
                        $options.Add("MOVE-''$($DatabaseName)_log''-TO", "$($defaultTargetLocations.DefaultData)\$($RestoreAs)_log.ldf")
                    }
                }
            }

            if ($DecryptionCredential -and -not($options.Contains('PASSWORD')))
            {
                $options.Add('PASSWORD', $DecryptionCredential.Password)
            }

            $arguments = Get-RedgateSQLBackupParameter -Parameters $options

            $restoreCommand = "EXEC master..sqlbackup '-SQL ""RESTORE $backupType [$(@{$true=$DatabaseName;$false=$RestoreAs}[-not ($RestoreAs)])] FROM $Disks $arguments""'"


            if ($PSCmdlet.ShouldProcess($TargetSQLServerName, $restoreCommand))
            {
                $command = "
                        DECLARE @errorcode INT
                              , @sqlerrorcode INT
                        $($restoreCommand), @errorcode OUTPUT, @sqlerrorcode OUTPUT;
                        IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
                        BEGIN
                            RAISERROR ('SQL Backup failed with exit code: %d; SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode);
                        END"

                $params = @{
                    ServerInstance = $TargetSQLServerName
                    Database = 'master'
                    Query = $command
                    Credential = $Credential
                    QueryTimeout = 7200
                    As = 'SingleValue'
                }

                #This will output the command in a formatted way
                Write-Verbose ("`n`nRestoring database $DatabaseName to $TargetSQLServerName`n")
                Write-Verbose ($command.Replace(',', "`n,") | Out-String)

                try
                {
                    $result = Invoke-Sqlcmd2 @params -ErrorAction Stop
                }
                catch
                {
                    # exception message comes through like this: Exception calling "Fill" with "1" argument(s): "SQL Backup failed with exit code: 850; SQL error code: 0"
                    # we want to get the SQL Backup exit code, and the SQL error code.
                    $message = $PSItem.Exception.Message.TrimEnd('"')
                    $message = $message.Split('"')[-1]
                    $errors = $message.Split(';').Trim()
                    foreach ($item in $errors)
                    {
                        $properties = $item.Split(':').Trim()
                        $errorLabel = $properties[0]
                        $errorNumber = $properties[1]
                        if ( $errorLabel -eq 'SQL Backup failed with exit code' -and $errorNumber -ne 0)
                        {
                            Write-Warning "SQL Backup failed with exit code: `n`n$(Get-RedgateSQLBackupError -ErrorNumber $errorNumber)"
                        }
                        elseif ($errorNumber -gt 0)
                        {
                            Write-Warning "$errorLabel : `n`n $errorNumber"
                        }
                    }
                    throw [System.Exception] 'There were errors performing the restore. See warnings above.'
                }
                Write-Verbose ($result | Out-String)
            }
        }
        catch
        {
            throw $PSItem.Exception
            break
        }
    }
}
# .\PSRedgate\public\Set-RedgateMultiScriptConfigurationFileInfo.ps1
function Set-RedgateMultiScriptConfigurationFileInfo
{
    <#
    .SYNOPSIS
    This cmdlet is used to generate a new Application.dat file or a Distribution List for Redgate's SQL Multi Script application.

    .DESCRIPTION
    We have the need to dynamically generate configuration files for Redgate's SQL Multi Script because sometimes environments change and
    manually having to configure the application can be difficult and time consuming. This cmdlet will allow you to quickly and easily create
    a config with many servers or databases in them. This cmdlet is designed to work will with dbatools (www.dbatools.io) using their Get-DBADatabase
    cmdlet.

    .EXAMPLE


#>
    [CmdletBinding(SupportsShouldProcess, DefaultParameterSetName = 'IntegratedSecurity')]
    param (
        # the location that you want the file to be written, by default will overwrite your current Application.dat
        [string]$Path,

        [Parameter()]
        # the distribution list that you would like to modify. If none is provided, it will default to the first one.
        [string]$DistributionList,

        [Parameter(ValueFromPipeline, ValueFromPipelineByPropertyName)]
        # the instance that you would like to create a connection to.
        [string]$SQLInstance,

        [Parameter(ValueFromPipeline, ValueFromPipelineByPropertyName)]
        # database name. It comes back as name from dbatools for some reason.
        [Alias('DatabaseName', 'Database')]
        [string]$Name,

        [Parameter()]
        [ValidateSet('ApplicationConfig', 'DistributionList')]
        # This will tell the cmdlet whether you want to load a pre-generated list of files, or change your default file.
        [string]$As = 'DistributionList',

        [Parameter(ParameterSetName = 'IntegratedSecurity')]
        #if passed, then use integrated, and don't require a username and password.
        [switch]$IntegratedSecurity,

        [Parameter(Mandatory, ParameterSetName = 'SQLAuth')]
        # the UserName to supply to every connection that is created using this method
        [string]$UserName,

        [Parameter(Mandatory, ParameterSetName = 'SQLAuth')]
        # the password for the user name specified
        [string]$encryptedKey,

        [Parameter(ParameterSetName = 'AutoSQLAuth')]
        # tells the cmdlet that you would like to use a set of already existing credentials.
        [switch]$PromptForCredentials,

        [Parameter()]
        # This is a collection that you can pass in to overwrite the defaults.
        [System.Collections.HashTable] $ApplicationParameters,

        [Parameter()]
        # required to overwrite an existing file.
        [switch]$Force
    )
    BEGIN
    {
        try
        {
            $applicationInfo = Get-RedGateInstallationInfo -ApplicationName 'SQL Multi Script'

            if (-not($applicationInfo))
            {
                Write-Warning 'SQL Multi Script does not appear to be installed on this machine.'
                break
            }

            if (-not($Path))
            {
                $Path = Get-RedgateMultiScriptConfigurationFileInfo -FileLocation
            }

            $existingConfigFile = Test-Path $Path

            if ($PromptForCredentials)
            {
                if ($existingConfigFile)
                {
                    $result = Get-RedgateMultiScriptConfigurationFileInfo -ReturnType 'SavedCredentials'
                    Where-Object integratedSecurity -eq 'False' |
                        Select-Object 'username', 'passwordValue' -Unique |
                        Out-GridView -OutputMode Single -Title 'Select an embedded credential that you would like to use'

                    $UserName = $result.username
                    $encryptedKey = $result.passwordValue
                }
                else
                {
                    Write-Warning "Unable to retrieve stored credentials, the $Path file does not exist. Create a starting file with the credentials for this to work."
                }
            }

            if (($DistributionList))
            {
                $guid = [guid]::NewGuid()
            }
            elseif ($existingConfigFile)
            {
                $result = Get-RedgateMultiScriptConfigurationFileInfo -ReturnType 'DistributionLists' |
                    Select-Object -First 1
                $DistributionList = $result.name
                $guid = $result.guid
            }
            else
            {
                $DistributionList = 'Default Distribution List'
                $guid = [guid]::NewGuid()
            }



            $fileContent = "<?xml version=`"1.0`" encoding=`"utf-16`" standalone=`"yes`"?>"

            if ($As -eq 'ApplicationConfig')
            {
                Write-Verbose 'Creating application configuration file.'
                $defaultParameters = @{
                    executionTimeout           = 0
                    batchSeparator             = 'GO'
                    displayFormat              = 0
                    maximumNonXMLDataRetrieved = 65535
                    maximumXMLDataRetrieved    = 2097152
                    maximumCharactersPerColumn = 256
                    maximumParallelServers     = 5
                    useParallelExecution       = 'True'
                    scriptEncoding             = 1252
                }

                # this will overwrite any of the values in the hash above if someone specified something they want to overwrite.
                if ($ApplicationParameters)
                {
                    Write-Verbose 'Overwriting default values with values from -ApplicationParameters'
                    $ApplicationParameters.GetEnumerator() | ForEach-Object {
                        $defaultParameters.item($PSItem.Name) = $PSItem.Value
                    }
                }


                Write-Verbose 'Generating list of application options.'
                $fileContent += "<multiScriptApplication version=`"4`" type=`"multiScriptApplication`">
                                    <addedServers type=`"List_server`" version=`"1`" />
                                    <applicationOptions version=`"4`" type=`"applicationOptions`">"
                foreach ($parameter in $defaultParameters.GetEnumerator())
                {
                    $fileContent += "<$($PSItem.Name)>$($PSItem.Value)</$($PSItem.Name)>"
                }
                $fileContent += '</applicationOptions>
                                 <currentProject />'
            }
            elseif ($As -eq 'DistributionList')
            {
                $fileContent += "<databaseListsFile version=`"1`" type=`"databaseListsFile`"> "

            }

            $fileContent += "
              <databaseLists type=`"List_databaseList`" version=`"1`">
                <value version=`"2`" type=`"databaseList`">
                  <name>$DistributionList</name>
                  <databases type=`"BindingList_database`" version=`"1`">"
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break
        }
    }
    PROCESS
    {
        try
        {
            if ($IntegratedSecurity)
            {
                $fileContent += "<value version=`"5`" type=`"database`">
                <name>$Name</name>
                <server>$SQLInstance</server>
                <integratedSecurity>True</integratedSecurity>
                <connectionTimeout>15</connectionTimeout>
                <protocol>-1</protocol>
                <packetSize>4096</packetSize>
                <encrypted>False</encrypted>
                <selected>True</selected>
                <cserver>$SQLInstance</cserver>
                </value>"
            }
            else
            {
                $fileContent += "
                <value version=`"5`" type=`"database`">
                <name>$Name</name>
                <server>$SQLInstance</server>
                <integratedSecurity>False</integratedSecurity>
                <username>$UserName</username>
                <savePassword>True</savePassword>
                <password encrypted=`"1`">$encryptedKey</password>
                <connectionTimeout>15</connectionTimeout>
                <protocol>-1</protocol>
                <packetSize>4096</packetSize>
                <encrypted>False</encrypted>
                <selected>True</selected>
                <cserver>$SQLInstance</cserver>
                </value>"
            }


        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break
        }
    }
    END
    {
        try
        {

            $fileContent += " </databases>
                            <guid>$guid</guid>
                            </value>
                            </databaseLists>"


            if ($As -eq 'ApplicationConfig')
            {
                $fileContent += "<autoCloseSaveAllResultsProgressDialog>False</autoCloseSaveAllResultsProgressDialog>
                            </multiScriptApplication>"
            }
            elseif ($As -eq 'DistributionList')
            {
                $fileContent += '</databaseListsFile>'
            }

            if ($Path)
            {
                if (-not(Test-Path $Path))
                {
                    Write-Verbose "Creating file for config file or distribution list."
                    New-Item $Path -ItemType File
                }

                Set-Content -Value $fileContent -LiteralPath $Path
            }
            else
            {
                Write-Output $fileContent
            }
        }
        catch
        {
            Write-Output $PSItem.Exception | Format-List -Force
            break

        }
    }
}
Write-Verbose 'Importing from [C:\projects\psredgate\PSRedgate\classes]'