Sample SCA Build of Migrations-First project.sql

param
(
      [string]$project = 'C:\Users\kendar.DERP\Documents\Visual Studio 2017\Projects\BabbyNames2017\BabbyNames2017\BabbyNames2017.sqlproj',
 
      [string]$packageVersion = '0.0.005',
      [string]$packageID = 'BN',
      [string]$buildArtifactPath = 'C:\DBAutomation\BuildArtifacts',
      [string]$buildSQLServer  = '.\DEV',   # for named instance do '.\instancename'
      [string]$releaseArtifactPath = 'C:\DBAutomation\ReleaseArtifacts',
      [string]$targetSQLServer  = '.\DEV',
      [string]$targetDatabase = 'BabbyNamesNotQuiteEmpty',
      [string]$sqlCompareOptions = '' #'ObjectExistenceChecks' #'NoTransactions'


)

$errorActionPreference = "stop"


# Invoke the build
$validatedProject = $project | Invoke-DatabaseBuild -TemporaryDatabaseServer "Data Source =$buildSQLServer"

# Create a database build artifact
$buildArtifact = $validatedProject | New-DatabaseBuildArtifact -PackageId $packageID -PackageVersion $packageVersion

# Export the build artifact
$buildArtifact | Export-DatabaseBuildArtifact -Path "$buildArtifactPath"

# We need to connect to the database we plan to deploy to to compare our build with its current state
$targetConnection = New-DatabaseConnection -ServerInstance $targetSQLServer -Database $targetDatabase

# Create the release artifact
$releaseArtifact = New-DatabaseReleaseArtifact -Source $buildArtifact -Target $targetConnection -SQLCompareOptions $sqlCompareOptions

# Export it to the file system for posterity
$releaseArtifact | Export-DatabaseReleaseArtifact -Path "$releaseArtifactPath\$targetDatabase\$packageID.$packageVersion\"


# Perform the deployment
Import-DatabaseReleaseArtifact -Path "$releaseArtifactPath\$targetDatabase\$packageID.$packageVersion\" | Use-DatabaseReleaseArtifact -DeployTo $targetConnection