Using Unit Test in the DevOps process

Last modified: March 28, 2025

Unit Test for SQL Server can be directly involved in the Continuous Integration (CI) process via the PowerShell cmdlets that can help automate to create and run unit tests. Unit Tests allow verifying the changes made by developers and validating that the applied changes do not affect the database after its deployment.

To start using Unit Test in the DevOps process, be sure that:

dbForge DevOps Automation for SQL Server and dbForge Unit Test for SQL Server are installed on your computer. For more information about the installation of dbForge Unit Test, see Installing.
PowerShell version is 3.0 or higher.
The tSQLt framework is installed for the database against which you want to run unit tests. For more information about how to install the framework, see How to install the tSQLt framework.
Unit tests have been created in the database. For more information about how to create tests, see the Save Unit Tests to Drive section in Database Continuous Integration with Devart Tools.

After the tests were created, you can add them to the database with the Invoke-DevartExecuteScript cmdlet and then run with the Invoke-DevartDatabaseTests cmdlet.

Note

By default, PowerShell is installed on Windows 8, 8.1, 10, and 11. For other versions, download PowerShell from the Microsoft official download center page.

To check the current version of PowerShell, open WindowsPowerShell ISE and execute the $PSVersionTable.PSVersion command. In the Major column, you can see the version currently installed on your computer.

Installing dbForge DevOps Automation PowerShell for SQL Server

dbForge DevOps Automation for SQL Server is supplied as part of dbForge SQL Tools (Professional version) and automatically includes dbForge DevOps Automation PowerShell for SQL Server.

To install dbForge DevOps Automation PowerShell for SQL Server

1. Go to the download page and click Get for free.

2. On the Devart Identity Server page, sign in to your account and then click Download. If you don’t have a Devart account, then you need to sign up.

3. After the download is complete, go to the folder with the downloads and run the sqltoolspro.exe file to install dbForge DevOps Automation PowerShell for SQL Server.

4. In the dbForge SQL Tools Professional installer, click Install.

Note

Prior to installing the tool, be sure that SSMS instances are closed.

5. On the Select Products page, make sure that the dbForge DevOps Automation PowerShell for SQL Server check box is selected. Then, click Next.

Note

If you have not installed dbForge Unit Test for SQL Server yet, then in this step select the dbForge Unit Test for SQL Server check box as well.

6. After the installation is complete, click Finish. The Devart.DbForge.DevOpsAutomation.SqlServer module with a set of cmdlets appears in Windows PowerShell ISE.

Installing dbForge DevOps Automation PowerShell for SQL Server

You can check the full list of requirements to run dbForge DevOps Automation on the Requirements and Compatibility page.

Creating unit tests using the Invoke-DevartExecuteScript cmdlet

The Invoke-DevartExecuteScript cmdlet creates tests in the database from the existing SQL files.

To create unit tests using PowerShell cmdlet

1. Run Windows PowerShell ISE on your computer.

2. In Windows PowerShell ISE, execute the PowerShell script to create unit tests.

# Defining variables
$ServerName = "JORDANS\MSSQLSERVER2019"
$DatabaseName = "AdventureWorks2019"
$Connection = New-DevartSqlDatabaseConnection -Server $ServerName -Database $DatabaseName  -WindowsAuthentication true

#Adding tSQLt tests into a database
Invoke-DevartExecuteScript -Connection $Connection -Input 'D:\Source_TUnit_tests'

Parameters used in the script

Parameter	Description
-Input	Folder containing SQL or ZIP files with unit tests.
-Connection	Object for connecting to the database for which the tests will be created.

Running unit tests using the Invoke-DevartDatabaseTests cmdlet

The Invoke-DevartDatabaseTests cmdlet allows running tSQLt unit tests against a database.

To run unit tests using PowerShell cmdlet

1. Run Windows PowerShell ISE on your computer.

2. In Windows PowerShell ISE, execute the PowerShell script to run unit tests.

# Defining variables
$ServerName = "JORDANS\MSSQLSERVER2019"
$DatabaseName = "AdventureWorks2019"
$Connection = New-DevartSqlDatabaseConnection -Server $ServerName -Database $DatabaseName  -WindowsAuthentication true
$ReportFile = "D:\Reports\TestReport"

# Running tests
Invoke-DevartDatabaseTests -InputObject $Connection -InstalltSQLtFramework -OutReportFileName $ReportFile -ReportFormat JUnit

Parameters used in the script

Parameter	Description
-InputObject	Connection string for the database against which tSQLt tests will be run.
-InstalltSQLtFramework	Installs a tSQLt framework before running the tests.
-OutReportFileName	Path to the output report file with the test results.
-ReportFormat	Specifies the format for the output report file.
Invoke-DevartExecuteScript	Executes a SQL script.
Invoke-DevartDatabaseTests	Executes the tSQLt tests from the database project. The results can be generated in a report.