Database Continuous Integration – DeployTest

Database Continuous Integration – DeployTest

by JBrooks 23. February 2017 15:44

Part 4 of 4 – Main PowerShell Script

You can download all of the code here. Below is the main PowerShell script called Process-InBasket.ps1.  It might be scheduled to run every few minutes. It will look for new commands and if any it will call OneScriptClient passing in the parameters found in the command.

Then it will run if it finds a new *.sql file in the In-Basket directory. It will restore a resent backup of production, apply the script and then email the developers with “Success” or “Failed” in the subject. The body of the email will be the log and attached will be a file with the results of the script running.

<# .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"))} }

Tags:

PowerShell | Continuous Integration | Development | OneScript

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.

RecentPosts