Invoke-DlmDatabaseTests

Runs the tSQLt tests on a database schema.

Syntax

Description

The Invoke-DlmDatabaseTests cmdlet executes the tSQLt tests on a database schema. The schema can be a scripts folder, a NuGet package or a database. It creates a TestResults object that represents the results of the tests.

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

If you use a scripts folder or a NuGet package 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.

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-DlmDatabaseConnection 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 Database Package object produced by the New-DlmDatabasePackage 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.DLMAutomation.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, DLM Automation uses LocalDB 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, DLM 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.DLMAutomation.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-DlmDatabaseConnection 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, DLM Automation uses LocalDB 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. DLM Automation applies a default set of options, 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/dlma/compareoptions for a complete list of SQL Compare options supported by DLM Automation.

-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-DlmDatabaseConnection 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 Database Package object produced by the New-DlmDatabasePackage 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.DLMAutomation.Compare.SchemaTesting.TestResults

Examples

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

$database = New-DlmDatabaseConnection -ServerInstance "test01\sql2014" -Database "Test"
$testResults = Invoke-DlmDatabaseTests $database

This example shows how to use the Invoke-DlmDatabaseTests cmdlet to run tSQLt tests on a database.

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

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

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

This example shows how to use the Invoke-DlmDatabaseTests cmdlet to run tSQLt tests for a Redgate Database Project.

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

$scriptsFolder = "C:\Work\scripts"
$testResults = Invoke-DlmDatabaseTests $scriptsFolder

This example shows how to use the Invoke-DlmDatabaseTests cmdlet to run tSQLt tests for a scripts folder.

The cmdlet will use LocalDB to run the tests on.

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

$scriptsFolder = "C:\Work\scripts"
$testResults =  Invoke-DlmDatabaseTests $scriptsFolder -IncludeTestData

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

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

$scriptsFolder = "C:\Work\scripts"
$testResults =  Invoke-DlmDatabaseTests $scriptsFolder -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-DlmDatabaseTests $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 ----------

$scriptsFolder = "C:\Work\scripts"
$testResults = $scriptsFolder | Invoke-DlmDatabaseTests -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 ----------

$scriptsFolder = "C:\Work\scripts"
$temporaryDatabase = New-DlmDatabaseConnection -ServerInstance "temp01\sql2014" -Database "TemporaryDb"
$testResults = $scriptsFolder | Invoke-DlmDatabaseTests -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 ----------

$scriptsFolder = "C:\Work\scripts"
$options = "IgnoreComments, ObjectExistenceChecks, -IgnoreUserProperties"
$testResults = $scriptsFolder | Invoke-DlmDatabaseTests -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.