Add-PSSnapin -Name "Microsoft.SharePoint.PowerShell"


function Invoke-NonQuery
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionString")]
        [string]$ConnectionString,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$DatabaseName,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$DatabaseServer,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionString")]
        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$Query,

        [Parameter(Mandatory=$false, ParameterSetName = "ConnectionString")]
        [Parameter(Mandatory=$false, ParameterSetName = "ConnectionComponents")]
        [int]$CommandTimeout = 30
    )

    begin
    {
        if( $PSCmdlet.ParameterSetName -eq "ConnectionComponents" )
        {
            $connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Pooling=false"
        }
    }
    process
    {
        Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Query: $Query"

        try
        {
            $connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
            $connection.Open()

            $command = New-Object System.Data.SqlClient.SqlCommand($Query, $connection)
            $command.CommandTimeout = $CommandTimeout
            $command.CommandType = [System.Data.CommandType]::Text
            $command.ExecuteNonQuery() | Out-Null
        }
        catch
        {
            Write-Error -Message "Error executing query '$Query'. Exception: $_. Connection String: $ConnectionString"
            throw $_.Exception
        }
        finally
        {
            if($command)
            {
                $command.Dispose()
            }

            if($connection)
            {
                [System.Data.SqlClient.SqlConnection]::ClearPool($connection)
                $connection.Close()
                $connection.Dispose()
            }
        }
    }
    end
    {
    }
}

function Get-DataTable
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][string]$DatabaseServer,
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$Query
    )

    $connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Enlist=False;Connect Timeout=5"

    Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Query: $Query"

    try
    {
        $dataSet     = New-Object System.Data.DataSet
        $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Query, $connectionString)
        $dataAdapter.Fill($dataSet) | Out-Null
        return $dataSet.Tables[0]
    }
    catch
    {
        Write-Error -Message "Error executing query '$Query'. Exception: $_. Connection String: $connectionString"
        throw $_.Exception
    }
    finally
    {
        if($dataSet)
        {
            $dataSet.Dispose()
        }

        if($dataAdapter)
        {
            $dataAdapter.Dispose()
        }
    }
}

function Invoke-ScalarQuery
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$DatabaseServer,
        [Parameter(Mandatory=$true)][string]$Query,
        [int]$CommandTimeout=30 # The default is 30 seconds
    )

    $connectionString = "Data Source=$DatabaseServer;Initial Catalog=$DatabaseName;Integrated Security=True;Enlist=False;Connect Timeout=5"

    Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Query: $Query"

    try
    {
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        $connection.Open()

        $command = New-Object system.Data.SqlClient.SqlCommand($Query, $connection)
        $command.CommandTimeout = $CommandTimeout
        $result = $command.ExecuteScalar()

        return $result
    }
    catch
    {
        Write-Error -Message "Error executing query '$Query'. Exception: $_. Connection String: $connectionString"
        throw $_.Exception
    }
    finally
    {
        if($connection)
        {
            $connection.Close()
            $connection.Dispose()
        }
    }
}

function Remove-Database
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionString")]
        [string]$ConnectionString,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$DatabaseName,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$DatabaseServer
    )

    begin
    {
    }
    process
    {
        if( $PSCmdlet.ParameterSetName -eq "ConnectionString" )
        {
            $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($ConnectionString)
            $DatabaseName   = $builder.InitialCatalog
            $DatabaseServer = $builder.DataSource
        }

        try
        {
            Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "ALTER DATABASE [$DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -CommandTimeout 0
        }
        catch
        {
            Write-Error -Message "Error setting database $DatabaseName into SINGLE_USER mode. Exception: $_"
            throw $_.Exception
        }

        try
        {
            Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "DROP DATABASE [$DatabaseName]" -CommandTimeout 0
        }
        catch
        {
            Write-Error -Message "Error dropping database $DatabaseName. Exception: $_"
            throw $_.Exception
        }
    }
    end
    {
    }
}

function Backup-Database
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionString")]
        [string]$ConnectionString,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$DatabaseName,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [string]$DatabaseServer,

        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionComponents")]
        [Parameter(Mandatory=$true, ParameterSetName = "ConnectionString")]
        [string]$FilePath
    )

    begin
    {
    }
    process
    {
        if( $PSCmdlet.ParameterSetName -eq "ConnectionString" )
        {
            $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($ConnectionString)
            $DatabaseName   = $builder.InitialCatalog
            $DatabaseServer = $builder.DataSource
        }

        try
        {
            Invoke-NonQuery -DatabaseName $DatabaseName -DatabaseServer $DatabaseServer -Query "BACKUP DATABASE [$DatabaseName] TO DISK = N'$FilePath' WITH NOFORMAT, NOINIT, NAME = N'$DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD" -CommandTimeout 0
        }
        catch
        {
            Write-Error -Message "Error backing up database $DatabaseName to $FilePath. Exception: $_"
            throw $_.Exception
        }
    }
    end
    {
    }
}

function Get-DatabaseAvailabilityGroupName
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory=$true)][string]$DatabaseServer,
        [parameter(Mandatory=$true)][string]$DatabaseName
    )

    begin
    {
    }
    process
    {
        Get-DataTable -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT name FROM sys.availability_databases_cluster c JOIN sys.availability_groups g ON g.group_id = c.group_id WHERE database_name = '$DatabaseName'" | SELECT -First 1 -ExpandProperty name
    }
    end
    {
    }
}

function Test-DatabaseName
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$DatabaseServer
    )

    try
    {
        [Convert]::ToBoolean( (Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT COUNT(*) FROM sys.databases (nolock) WHERE [Name] = '$DatabaseName'") )
    }
    catch
    {
        Write-Error -Message "Error testing database name '$DatabaseName'. Exception: $_"
        throw $_
    }
}

function Get-DatabaseFileMoveSyntax
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][object[]]$FileNameInfo,
        [Parameter(Mandatory=$true)][string]$TargetLogFilePath,
        [Parameter(Mandatory=$true)][string]$TargetDataFilePath
    )

    $sqlSyntax = ""

    try
    {
        $FileNameInfo | % {

            $physicalNameInfo = New-Object System.IO.FileInfo($_.PhysicalName)
            $logicalFileName  = $_.LogicalName
            $filePath         = ""

            switch($_.Type)
            {
                "D" # data file
                {
                    $filePath = [System.IO.Path]::Combine( $TargetDataFilePath, $physicalNameInfo.Name )
                }
                "L" # log file
                {
                    $filePath = [System.IO.Path]::Combine( $TargetLogFilePath, $physicalNameInfo.Name )
                }
            }

            $sqlSyntax += "MOVE N'$logicalFileName' TO N'$filePath', "
        }

        return $sqlSyntax
    }
    catch
    {
        Write-Error -Message "Error generating move database file syntax. Exception: $_"
        throw $_.Exception
    }
}

function Get-DatabaseBackupFileInfo
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][string]$DatabaseServer,
        [Parameter(Mandatory=$true)][string]$BackupFilePath
    )

    try
    {
        Get-DataTable -DatabaseServer $DatabaseServer -DatabaseName "master" -Query "RESTORE FILELISTONLY FROM DISK = N'$BackupFilePath' WITH NOUNLOAD" | SELECT LogicalName, PhysicalName, Type
    }
    catch
    {
        Write-Error -Message "Error reading database backup file info. Exception: $_"
        throw $_.Exception
    }
}

function Restore-Database
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$DatabaseServer,
        [Parameter(Mandatory=$true)][string]$BackupFilePath,
        [Parameter(Mandatory=$false)][string]$DataFilePath,
        [Parameter(Mandatory=$false)][string]$LogFilePath
    )

    try
    {
        # if the database exists, we can't restore it
        if( Test-DatabaseName -DatabaseName $databaseName -DatabaseServer $DatabaseServer )
        {
            throw "A database with the name '$databaseName' already exists on server $DatabaseServer"
        }

        if( -not $DataFilePath )
        {
            Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Getting data file path"

            # get the default log file path
            $DataFilePath = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH')" -Verbose
        }

        if( -not $LogFilePath )
        {
            Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Getting log file path"

            # get the default data file path
            $LogFilePath = Invoke-ScalarQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "SELECT SERVERPROPERTY('INSTANCEDEFAULTLOGPATH')" -Verbose
        }

        Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Data file path: $DataFilePath"
        Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Log file path:  $LogFilePath"

        # pull the logical and physical file names from the backup file
        $fileNameInfo = Get-DatabaseBackupFileInfo -DatabaseServer $DatabaseServer -BackupFilePath $BackupFilePath

        # get the sql syntax required to point the data and log files to the appropriate locations
        $fileMoveSyntax = Get-DatabaseFileMoveSyntax -FileNameInfo $fileNameInfo -TargetLogFilePath $LogFilePath -TargetDataFilePath $DataFilePath

        Invoke-NonQuery -DatabaseName "master" -DatabaseServer $DatabaseServer -Query "RESTORE DATABASE [$databaseName] FROM DISK = N'$BackupFilePath' WITH RECOVERY, $fileMoveSyntax NOUNLOAD" -CommandTimeout 0
    }
    catch
    {
        Write-Error -Message "Error restoring database $($DatabaseName) from $BackupFilePath. Exception: $_"
        throw $_
    }
}

function Move-SPContentDatabase
{
    [cmdletbinding()]
    param
    (
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$TargetServer,
        [Parameter(Mandatory=$true)][string]$BackupPath,
        [Parameter(Mandatory=$false)][string]$TargetAGName,
        [Parameter(Mandatory=$false)][switch]$RemoveSourceDatabase,
        [Parameter(Mandatory=$false)][string]$DataFilePath,
        [Parameter(Mandatory=$false)][string]$LogFilePath

    )

    begin
    {
        # bind to the source database
        $sourceDatabase = Get-SPContentDatabase -Identity $DatabaseName -ErrorAction SilentlyContinue
    }
    process
    {
        if( $sourceDatabase )
        {
            # grab the settings from the source database before doing anything

                $webApplication = $sourceDatabase.WebApplication
                $maxSiteCount   = $sourceDatabase.MaximumSiteCount
                $minSiteCount   = $sourceDatabase.WarningSiteCount
                $connectionStr  = $sourceDatabase.DatabaseConnectionString
                $databaseServer = $sourceDatabase.Server


            # REMOVE FROM CURRENT AVAILABILITY GROUP

                try
                {
                    $databaseAvailabilityGroupName = Get-DatabaseAvailabilityGroupName -DatabaseServer $databaseServer -DatabaseName $DatabaseName

                    if( $databaseAvailabilityGroupName )
                    {
                        $err = $null

                        Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Removing database from Availability Group"

                        Remove-DatabaseFromAvailabilityGroup -AGName $databaseAvailabilityGroupName -DatabaseName $DatabaseName -Force -ErrorVariable err

                        if( $err )
                        {
                            throw $err
                        }
                        Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Database removed from Availability Group"
                    }
                }
                catch
                {
                    Write-Error -Message "Error removing database $($DatabaseName) from Availalbity Group. Exception: $_"
                    return
                }


            # DETACH SOURCE DATABASE FROM SHAREPOINT

                try
                {
                    $sourceDatabase | Dismount-SPContentDatabase -Confirm:$false
                }
                catch
                {
                    Write-Error -Message "Error removing database $($DatabaseName) from SharePoint. Exception: $_"
                    return
                }


            # BACKUP SOURCE DATABASE

                try
                {
                    $databaseBackupFilePath = Join-Path -Path $BackupPath -ChildPath "$($DatabaseName)_$(Get-Date -Format 'yyyyMMdd-hhmmss').bak"

                    Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Backing up database to $databaseBackupFilePath"
                    Backup-Database -DatabaseName $DatabaseName -DatabaseServer $databaseServer -FilePath $databaseBackupFilePath
                }
                catch
                {
                    Write-Error -Message "Error backing up database $($DatabaseName) to $databaseBackupFilePath. Exception: $_"
                    return
                }


            # DROP SOURCE DATABASE

                if( $RemoveSourceDatabase )
                {
                    try
                    {
                        # drop content database from SQL
                        Remove-Database -DatabaseName $DatabaseName -DatabaseServer $databaseServer
                    }
                    catch
                    {
                        return
                    }
                }



            # RESTORE SOURCE DATABASE TO TARGET SQL INSTANCE

                try
                {
                    if( Test-Path -Path $databaseBackupFilePath -PathType Leaf )
                    {
                        Write-Verbose "$(Get-Date): $($MyInvocation.MyCommand.Name) - Restoring database to $TargetServer"
                        Restore-Database -DatabaseName $DatabaseName -DatabaseServer $TargetServer -BackupFilePath $databaseBackupFilePath -DataFilePath $DataFilePath -LogFilePath $LogFilePath
                    }
                    else
                    {
                        Write-Error -Message "Backup file not found: $databaseBackupFilePath"
                        return
                    }
                }
                catch
                {
                    Write-Error -Message "Error restoring database $DatabaseName to $TargetServer. Error: $_"
                    return
                }


            # ATTACH DATABASE TO SHAREPOINT

                try
                {
                    $targetContentDatabase = Mount-SPContentDatabase -WebApplication $webApplication -Name $DatabaseName -DatabaseServer $TargetServer -MaxSiteCount $maxSiteCount -WarningSiteCount $minSiteCount -SkipIntegrityChecks
                }
                catch
                {
                    Write-Error -Message "Error mounting database $DatabaseName to SharePoint. Error: $_"
                    return
                }


            # ADD DATABASE TO NEW AG

                try
                {
                    if( $targetContentDatabase -and $TargetAGName )
                    {
                        Add-DatabaseToAvailabilityGroup -AGName $TargetAGName -DatabaseName $DatabaseName -FileShare $BackupPath
                    }
                }
                catch
                {
                    Write-Error -Message "Error adding database to Availability Group. Error: $_"
                    return
                }
        }
        else
        {
            Write-Error "Database not found: $DatabaseName"
        }
    }
    end
    {
    }
}

# it's required that the source and target SQL service accounts have write and read perms, respectively, to the backup share path
Move-SPContentDatabase -DatabaseName "SCRIPT_TEST_003" -TargetServer "SQL-0" -BackupPath "\\sps-web-0\Backups" -TargetAGName "sp2013agau-ag" -RemoveSourceDatabase -Verbose