If you're unfamiliar with PowerShell, this page contains some general information and advice that will help you learn how to use the SQL Change Automation PowerShell cmdlets.

Open PowerShell ISE

Once you've installed SQL Change Automation, you can run the SQL Change Automation PowerShell cmdlets from any file location.

We recommend you run PowerShell ISE rather than basic PowerShell. PowerShell ISE has auto-complete behaviour that suggests available cmdlet names and parameters as you type.

To run PowerShell ISE:

• From the Start menu, type PowerShell ISE.
• Click Windows PowerShell ISE.

List all SQL Change Automation cmdlets

Run the following PowerShell code to see a list of all the SQL Change Automation cmdlets:

Get-Command -Module SQLChangeAutomation

Getting information about a cmdlet

To get information about a cmdlet, including examples and a list of all available parameters, enter:

Get-Help <cmdlet name> -Full

Get-Help Sync-DatabaseSchema -Full

Input objects

Unlike the command line, which can only input and output text strings, PowerShell cmdlets work with objects.

Some PowerShell cmdlets require an input object, which you can specify after the cmdlet name:

Invoke-DatabaseTests "C:\Work\project\project.sqlproj"

In the example above, we've used the Invoke-DatabaseTests cmdlet, which runs tSQLt tests on a database schema. The input object of the cmdlet is the SQL Change Automation project path C:\Work\project\project.sqlproj.

Variables

In PowerShell, you can assign values and objects to named variables, so you can reuse them in another PowerShell statement. Variable names are preceded by the dollar ($) symbol.

$project = "C:\Work\project\project.sqlproj"
Invoke-DatabaseTests $project

In the example above, we've assigned the scripts folder to the variable $project. We've then used this as the input object of the Invoke-DatabaseTest cmdlet.

By using variables, you can take the output of one cmdlet and use it as the input of another:

$project = "C:\Work\project\project.sqlproj"
$testResults = Invoke-DatabaseTests $project
Export-DatabaseTestResults $testResults -OutputFile "C:\Work\TestResults\results.junit.xml"

In the example above, we've assigned the output of the Invoke-DatabaseTests cmdlet to the variable, $testResults. We've then used this as the input object for the Export-DatabaseTestResults cmdlet. This cmdlet exports the test results to disk.

Cmdlet parameters

Most cmdlets use parameters that change their behaviour.

$testResults = Invoke-atabaseTests "C:\Work\project\project.sqlproj"
Export-DatabaseTestResults $testResults -OutputFile "C:\Work\TestResults\results.junit.xml"

In the example above, we've used the -OutputFile parameter of the Export-DatabaseTestResults cmdlet to specify the file path to export the test results to.

You can also use variables to specify parameter values:

$testResults = Invoke-DatabaseTests "C:\Work\project.sqlproj"
$testResultsFile = "C:\Work\TestResults\results.junit.xml"
Export-DatabaseTestResults $testResults -OutputFile $testResultsFile

Some parameters don't require a value. They're used to switch on or off a particular feature of the cmdlet:

$testResults = Invoke-DatabaseTests "C:\Work\project\project.sqlproj"
Export-DatabaseTestResults $testResults -OutputFile "C:\Work\TestResults\results.junit.xml" -Force

In the example above, we've used the -Force parameter of the Export-DatabaseTestResults cmdlet. This specifies that if the output file already exists, it should be overwritten.

Some parameters are always required when you use a cmdlet. Others parameters are optional. To get help on a particular parameter and find out if it's required, enter:

Get-Help <cmdlet name> -Full <parameter name>

Get-Help Export-DatabaseTestResults -Parameter Force

Piping cmdlets together

We've already seen how with the help of variables, you can use the output of one cmdlet as the input of another. Another way to do this is to use the pipe (|) symbol to connect cmdlets together in a single PowerShell statement. When you "pipe" two cmdlets together, you're telling PowerShell to take the first cmdlet's output and use it as the input for the second cmdlet:

Invoke-DatabaseTests "C:\Work\project\project.sqlproj" | Export-DatabaseTestResults -OutputFile "C:\Work\TestResults\results.junit.xml"

In the example above, we haven't specified an input object after the name of the Export-DatabaseTestResults cmdlet. Instead we've used the pipe symbol to tell PowerShell to use the output of the Invoke-DatabaseTests cmdlet as the input object.

Error action preference

PowerShell includes a set of preference variables that allow you to customize its behaviour. One of these is the error action preference variable. This determines what happens when there's an error in the script.

To make it easier to find errors, we recommend you set the error action preference variable to "stop". This means when a cmdlet encounters any kind of error, the PowerShell script will stop. To do this, at the beginning of your PowerShell script, enter the following:

$errorActionPreference = "stop"

What next?

You're now ready to look at the SQL Change Automation cmdlets in more detail. The best ones to start with are for Setting up database connections.