Invoke-DatabaseTests

Runs the tSQLt tests contained in a database project.

Syntax

Description

The Invoke-DatabaseTests cmdlet executes the tSQLt tests contained in a database project. It creates a TestResults object that represents the results of the tests.

If you use a path to a database project as the input, by default the cmdlet will create a temporary database on LocalDB to run the tests on. Alternatively, you can use the TemporaryDatabaseServer parameter to specify a SQL Server instance to create the temporary database on. This is useful if your database uses features that aren't supported by LocalDB, such as Full-Text Search. If you don't want to use LocalDB and don't have permission to create a database on the SQL Server instance, you can use the TemporaryDatabase parameter to specify an existing database.

If you use a database connection as the input, the cmdlet will run the tests directly on the specified database.

Parameters

-InputObject <System.Object>

The schema to run the tSQLt tests on.

This can be a scripts folder, a NuGet package or a database connection. This can be:

- a Database Connection object created by the New-DatabaseConnection cmdlet

- a database connection string

- a path for a NuGet package or .zip file. This must contain a scripts folder located at db\state

- a build artifact object produced by the New-DatabaseBuildArtifact cmdlet

- a path for a scripts folder, created by SQL Compare or from your SQL Source Control database repository

-IncludeTestData <System.Management.Automation.SwitchParameter>

Use this parameter to add random data to the test database.

You can have more control over the data being added, by using the SQLDataGeneratorProject parameter instead.

This parameter will be ignored if the InputObject is a database connection.

-SQLDataGeneratorProject <System.String>

Use this parameter to specify a SQL Data Generator project file (.sqlgen) that defines the data to be added to the test database.

To add random data to the test database, use the IncludeTestData parameter instead.

This parameter will be ignored if the InputObject is a database connection.

-RunOnly <System.String>

Use this parameter to run a specific test or a specific test suite. If you don't use this parameter, every test will be run.

-TemporaryDatabaseServer <RedGate.Versioning.Automation.Compare.SchemaSources.DatabaseServerConnection>

The connection string for the temporary database server used for testing. For example, 'Data Source=TempServer01'.

This parameter will be ignored if the InputObject is a database connection.

By default, LocalDB is used for the temporary database. However there may be some features in your database that aren't supported by LocalDB (for example, Full-Text Search). In this case, or if LocalDB is not present, use this parameter to specify an alternative SQL Server instance for the temporary database.

Using this option, SQL Change Automation will create a temporary, randomly-named database on the specified SQL Server instance.

You can't use this parameter in addition to the TemporaryDatabase parameter.

-TemporaryDatabase <RedGate.Versioning.Automation.Compare.SchemaSources.DatabaseConnection>

The details of the temporary database used for testing. This can be:

- a database connection object that contains the connection details for a database. See New-DatabaseConnection for details.

- a database connection string. For example, 'Data Source=TempServer01;Initial Catalog=TempDatabase01'.

This parameter will be ignored if the InputObject is a database connection.

By default, LocalDB is used for the temporary database. If you don't want to use LocalDB and don't have permission to create a database on the SQL Server instance, use this option to specify an existing database to use for the temporary copy of the database.

You can't use this parameter in addition to the TemporaryDatabaseServer parameter.

-SQLCompareOptions <System.String>

Specifies the SQL Compare options to use when creating the test database. A default set of options are listed below. To include additional options, specify a comma-delimited list of the option names (eg 'IgnoreComments, ObjectExistenceChecks'). To turn off a default option, precede the option name with a minus sign (eg '-ForceColumnOrder').

This parameter will be ignored if the InputObject is a database connection or if the value specified is either $null or empty.

By default, the following Compare options are used:

- ConsiderNextFilegroupInPartitionSchemes

- DecryptPost2KEncryptedObjects

- DoNotOutputCommentHeader

- ForceColumnOrder

- IgnoreCertificatesAndCryptoKeys

- IgnoreDatabaseAndServerName

- IgnoreUsersPermissionsAndRoleMemberships

- IgnoreUserProperties

- IgnoreWhiteSpace

- IgnoreWithElementOrder

- IncludeDependencies

- ThrowOnFileParseFailed

- UseCompatibilityLevel

See http://www.red-gate.com/sca/ps/help/compareoptions for a complete list of supported SQL Compare options.

-SQLDataCompareOptions <System.String>

Specifies the SQL Data Compare options to use when creating the script for validation. To include additional options, specify a comma-delimited list of the option names (eg 'DisableAndReenableDDLTriggers, CompressTemporaryFiles').

-FilterPath <System.String>

The path to a .scpf filter file.

Overrides any Filter.scpf file present in the InputObject schema with an alternative filter file to be used when generating the database to test against.

This parameter will be ignored if the InputObject is a database connection or if the value specified is either $null or empty.

-QueryBatchTimeout <System.Int32>

The execution timeout, in seconds, for each batch of queries when creating the test database. The default value is 30 seconds for queries and 900 seconds for the test run. A value of zero indicates that no execution timeout will be enforced.

This parameter will be ignored if the InputObject is a database connection.

-IgnoreParserErrors <System.Management.Automation.SwitchParameter>

If the SQL Compare engine should ignore parser errors when creating the test database. This parameter will be ignored if the InputObject is a database connection.

<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable. For more information, see http://technet.microsoft.com/en-us/library/hh847884.aspx.

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

• System.Object

  The schema to run the tSQLt tests on.

  This can be a scripts folder, a NuGet package or a database connection. This can be:

  - a Database Connection object created by the New-DatabaseConnection cmdlet

  - a database connection string

  - a path for a NuGet package or .zip file. This must contain a scripts folder located at db\state

  - a build artifact object produced by the New-DatabaseBuildArtifact cmdlet

  - a path for a scripts folder, created by SQL Compare or from your SQL Source Control database repository

Return values

The output type is the type of the objects that the cmdlet emits.

• RedGate.Versioning.Automation.Compare.SchemaTesting.TestResults

Examples

---------- EXAMPLE 1 ----------

$testResults = Invoke-DatabaseTests "C:\Work\testproject\AdventureWorks2014.Database.Tests\AdventureWorks2014.Database.Tests.sqlproj"

This example shows how to use the Invoke-DatabaseTests cmdlet to run tSQLt tests for a SQL Change Automation project.

---------- EXAMPLE 2 ----------

$project = "C:\Work\scripts"
$testResults = Invoke-DatabaseTests $project

This example shows how to use the Invoke-DatabaseTests cmdlet to run tSQLt tests for a SQL Source Control project.

The cmdlet will use LocalDB to run the tests on.

---------- EXAMPLE 3 ----------

$database = New-DatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"
$testResults = Invoke-DatabaseTests $database

This example shows how to use the Invoke-DatabaseTests cmdlet to run tSQLt tests already deployed to a database.

The output will be a TestResults object, which contains the test results.

---------- EXAMPLE 4 ----------

$project = "C:\Work\testproject\AdventureWorks2014.Database.Tests\AdventureWorks2014.Database.Tests.sqlproj"
$testResults =  Invoke-DatabaseTests $project -IncludeTestData

This example shows how to add random test data before the tests are run.

---------- EXAMPLE 5 ----------

$project = "C:\Work\scripts"
$testResults =  Invoke-DatabaseTests $project -SqlDataGeneratorProject 'C:\Work\DataGeneratorProject.sqlgen'

This example shows how to add test data defined by a SQL Data Generator project file (.sqlgen).

---------- EXAMPLE 6 ----------

$scriptsFolder = "C:\Work\scripts"
$testResults =  Invoke-DatabaseTests $scriptsFolder -RunOnly "[SQLCop].[test Unnamed Constraints]"

This example shows how to run the single test "[SQLCop].[test Unnamed Constraints]".

To run all tests in a single test suite, use the RunOnly parameter to specify the name of the test suite. For example: "[SQLCop]".

---------- EXAMPLE 7 ----------

$project = "C:\Work\testproject\AdventureWorks2014.Database.Tests\AdventureWorks2014.Database.Tests.sqlproj"
$testResults = $project | Invoke-DatabaseTests -TemporaryDatabaseServer "Data Source=temp01\sql2014"

This example shows how to specify a SQL Server instance for the temporary database, instead of using LocalDB.

This is useful if you don't want to use LocalDB because your database uses features that aren't supported by LocalDB, such as Full-Text Search.

---------- EXAMPLE 8 ----------

$project = "C:\Work\testproject\AdventureWorks2014.Database.Tests\AdventureWorks2014.Database.Tests.sqlproj"
$temporaryDatabase = New-DatabaseConnection -ServerInstance "temp01\sql2014" -Database "TemporaryDb"
$testResults = $project | Invoke-DatabaseTests -TemporaryDatabase $temporaryDatabase

This example shows how to specify an existing SQL Server database to use as the temporary database, instead of using LocalDB.

This is useful if you don't want to use LocalDB and you don't have permission to create a database on the SQL Server instance.

---------- EXAMPLE 9 ----------

$project = "C:\Work\testproject\AdventureWorks2014.Database.Tests\AdventureWorks2014.Database.Tests.sqlproj"
$options = "IgnoreComments, ObjectExistenceChecks, -IgnoreUserProperties"
$testResults = $project | Invoke-DatabaseTests -SQLCompareOptions $options

This example shows how to specify SQL Compare options to use when creating the test database.

In this example, the $options variable is used to specify that the IgnoreComments and ObjectExistenceChecks options should be included in addition to the default set of SQL Compare options used by this cmdlet. The minus sign before IgnoreUserProperties indicates that this default option will be turned off.