A PowerShell script to build, test and deploy your Redgate SQL Source Control scripts folder using the Redgate DLM Automation cmdlets

BuildWithDLMAutomation.ps1

# Script created 11/11/2016 by Alex Yates of DLM Consultants
# Shared freely, but a beer would be appreciated if you find this useful and we are ever in the same pub
# Full documentation for Redgate DLM Automation PowerShell cmdlets at:
# https://documentation.red-gate.com/display/DLMA2/Cmdlet+reference

# Variables (fill these in)

	# Required - the location of your source code
	$scriptsFolder = "C:\where\is\your\DB\source\code"
	
	# Required - a name and version number for your package (must not already exist in output directory)
	$packageID = "aNameForYourPackage"
	$packageVersion = 0.1
	
	# Required - An output directory in which to save your build artifacts (must already exist)
	$outputDir = "C:\JenkinsDrops"
	
	# Optional - If using an alternate SQL instance for schema validation provide details here. Also, uncomment this parameter from line 34 below.
	#$buildDb = "Data Source=.\SQL2014"
		
	# Required for sync step only - the database you wish to deploy to. Uncomment below and also lines 46-8 if running a sync step.
	#$targetServerInstance = ".\SQL2014"
	#$targetDatabaseName = "AdventureWorks"
	
	# Optional - If using SQL Auth for target DB add a username and password. Also, uncomment these parameters from line 42 below.
	#$username = "myUsername"
	#$password = "myPassword"

# Script to build DB (you can probably leave this as is)
	
	$errorActionPreference = "stop"
	 
	# Validate the scripts folder
	$validatedScriptsFolder = Invoke-DlmDatabaseSchemaValidation $scriptsFolder # -TemporaryDatabaseServer $buildDb

	# Export NuGet package
	$package = New-DlmDatabasePackage $validatedScriptsFolder -PackageId $packageID -PackageVersion $packageVersion
	Export-DlmDatabasePackage $package -Path $outputDir

# Script to run tests and/or deploy to an integration DB (uncomment as appropriate)

	# # Run tSQLt unit tests
	# Invoke-DlmDatabaseTests $package | Export-DlmDatabaseTestResults -OutputFile "$outputDir\$packageID.$packageVersion.junit.xml"

	# # Sync a test database
	# $targetDB = New-DlmDatabaseConnection -ServerInstance $targetServerInstance -Database $targetDatabaseName # -Username $username -Password $password
	# Test-DlmDatabaseConnection $targetDB
	# Sync-DlmDatabaseSchema -Source $package -Target $targetDB