Database Continuous Integration – DeployTest

<#
.SYNOPSIS
Process new SQL Scripts files by applying them to a SQL database.
.DESCRIPTION
Process-InBasket loads a Config file that controls all variables in the process. 
It then looks to a directory to search for new *.SQL files. 
Once a new file is found, a backup of the database is restored to SQL Server.
The script is then applied.
The process then looks for an SQL errors.
Then it sends an email of the log with "Success" or "Failure" in the subject.
.PARAMETER ConfigFile
The config file to use to process the deployment. Default: ./TestDeployConfig.confg
.EXAMPLE
PowerShell "C:\Work\Deploy\Process-InBasket.ps1" ./TestDeployConfig.xml


#>
[CmdletBinding()]
param (    
    [string] $ConfigFile='./TestDeployConfig.xml'
)

cls
$VerbosePreference = 'Continue'
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

# get a temp file name so we can start logging
$Private:logFileName = [IO.Path]::GetTempFileName()  

Set-Location $PSScriptRoot

# load the support scripts
. ".\DeployUtils\Load-DeployConfig.ps1"
. ".\DeployUtils\SQLServer.ps1"
. ".\DeployUtils\Utils.ps1"

Log "Root: $PSScriptRoot" $logFileName


Log ("Loading Config File $ConfigFile") -LogFileName $logFileName

#This will also create and load C.FileSettings.LogFileName
$Config = Load -ConfigFileName $ConfigFile -LogFileName $logFileName


Log "" 
Log ("Processing Matches: "+ $Config.C.FileSettings.InFileMask.InnerText)
$Private:processCount = 0
$Private:ret;

foreach($Private:CmdFile in (Get-ChildItem $Config.C.DirSettings.InCmdDir.InnerText))
{
    $content = Get-Content $CmdFile.FullName
    $cmdArgs = $content[0]
    $author = $content[1]
    $cmd = '"C:\Program Files\DigitalTools\OneScript Client\OneScriptCMD.exe" ' + `
        $cmdArgs + ' -do ' + $Config.C.DirSettings.InBasketDir.InnerText
    Invoke-Expression "& $cmd"  
    Move-Item -Path "$($CmdFile.FullName)" -Destination `
        $Config.C.DirSettings.ArchiveDir.InnerText -Force
   
}


foreach($Private:SQLFile in `
    (Get-ChildItem $Config.C.FileSettings.InFileMask.InnerText))
{
    Log ""
    Log ("Processing: "+ $SQLFile)    

    $Private:dt = Get-Date -Format yyyyMMdd_HHmmss
    
    
    $Private:temp = $Config.C.FileSettings.LogFileFormat.InnerText.Replace("{0}", $dt)
    
    if($processCount -eq 0)
    {
        Log 'Changing log file to $temp' 
     
        Move-Item -Path $logFileName -Destination $temp
        $Config.C.FileSettings.LogFileName = $temp

        $Private:DBSourceFile = Get-ChildItem `
            $Config.C.FileSettings.DBSourceFileMask.InnerText | `
            Sort-Object Name -Descending | Select-Object $_.Name -First 1

        Log ("SQL Backup File: " + $DBSourceFile)

        if(!$DBSourceFile)
        {
            Write-Error ("Searched: " + `
                $Config.C.FileSettings.DBSourceFileMask.InnerText)
            Write-Error "No SQL Backup File Found."
            Log "No SQL Backup File Found."
            Send-ToEmail  -Subject "Deploy Failed!, No SQL Backup File Found" `
                -LogFileName $Config.C.FileSettings.LogFileName
            exit -1
        }
        
    } 
    else
    {
        $Config.C.FileSettings.LogFileName = $temp
        Log(("Processing file number: " + ($processCount + 1).ToString())) 
        Log ("")
    }

    Log ("Log File: " + $Config.C.FileSettings.LogFileName)


    $Private:buildResultsFile = `
        $Config.C.FileSettings.BuildResultsFileFormat.InnerText.Replace("{0}", $dt)
    Log ("Build Results File: " + $buildResultsFile)

    $ret = RestoreSQLServer $Config.C.DBSettings.Server $Config.C.DBSettings.Database `
        $Config.C.DirSettings.DBFilesDir.InnerText $DBSourceFile
    
    if($ret -ne $true)
    {
        Write-Error "Restore Database Failed"
        Log "Restore Database Failed"
        Send-ToEmail -Subject "Deploy Failed!, Restore Database Failed" -LogFileName `
            $Config.C.FileSettings.LogFileName
        exit -1
    } 
        
    Log ("Running: "+ $SQLFile)    

    $ret = RunSQLFile $Config.C.DBSettings.Server $Config.C.DBSettings.Database `
        $SQLFile $buildResultsFile

    if($ret -ne $true)
    {
         Write-Error "Running SQL Script Failed"
         Log "Running SQL Script Failed"
         Send-ToEmail  -Subject "Deploy Failed!, Running SQL Script Failed" `
            -LogFileName $Config.C.FileSettings.LogFileName 
         exit -1
    } 

    Log "SQL Script Run Successful"

    $Private:hasError = CheckForErrors $buildResultsFile
    
    if($hasError -eq $true)
    {
         Write-Error "SQL Script Had Errors"
         Log "SQL Script Had Errors"
         Send-ToEmail  -Subject "Deploy Failed!, SQL Script Had Errors" `
            -LogFileName $Config.C.FileSettings.LogFileName `
            -Attachmentpath $buildResultsFile
    } 
    else
    {
        Log (("Deployment Successful"))    
        Log (("Completed " + $SQLFile))

        Send-ToEmail  -Subject "Deploy Script Successful" -LogFileName `
            $Config.C.FileSettings.LogFileName -Attachmentpath "$buildResultsFile";
    }

    Move-Item -Path "$buildResultsFile" -Destination `
        $Config.C.DirSettings.ArchiveDir.InnerText -Force

    $SQLFile = $SQLFile -replace ".sql$", ".*"
    Move-Item -Path "$SQLFile" -Destination `
        $Config.C.DirSettings.ArchiveDir.InnerText -Force

    $processCount = $processCount + 1
    
}

switch($processCount)
{
    0 {Log (("No files found to process"))}
    1 {Log (("1 Script Processed"))}
    default {Log (($processCount.ToString() + " Scripts Processed"))}
}