Using the build command
Published 11 July 2016
To run the SQL CI command line build step, use this syntax:
sqlCI.exe Build /scriptsFolder=<value1> /packageId=<value2> /packageVersion=<value3>
Example
sqlCI.exe Build /scriptsFolder=WidgetShop\Database\Scriptsfolder /packageId=WidgetShop /packageVersion=1.0
Here are details of all the build command switches:
/scriptsFolder
The path to the database scripts folder in source control.
Example
/scriptsFolder=WidgetShop\Database\Scriptsfolder
For the build VCS root, use a period (.):
/scriptsFolder=.
Contents:
/packageId
The name of the NuGet package you're creating.
Example
/packageId=WidgetShop
The name must not contain spaces.
/packageVersion
The package build number.
Example
/packageVersion=1.0
If you're running sqlCI.exe from the command line, you have to specify this number manually, but if you're running sqlCI.exe as part of an automated build, you can use the build number variable. To set the build number variable:
- in TeamCity, enter
$(build.number) - in CruiseControl.NET, enter
$(CCNetLabel) - in TFS 2008 or earlier, enter
($BuildNumber) - in TFS 2010 or later, follow the instructions in Passing TFS Build properties to MSBuild using TFS 2010 or later
- in Bamboo, enter
${bamboo.buildNumber}
If you're using a different build system, check the documentation provided by the vendor.
/temporaryDatabaseServer (optional)
The temporary database server name.
Example
/temporaryDatabaseServer=SQLServer2012
During the build step, SQL CI makes a copy of your database on a temporary server. By default, it uses LocalDB for this.
If your database uses features that are not supported by LocalDB, such as Full-Text Search, you can use this switch to specify the name of an alternative temporary server for the copy of the database.
For more information on LocalDB, see SQL Server 2012 Express LocalDB (MSDN article).
/temporaryDatabaseName (optional)
The name of an existing database on the temporary database server you previously specified.
Example
/temporaryDatabaseName=WidgetShopTemp
During the build step, SQL CI recreates your database on a temporary server. You should use an empty database for this, because data will be overwritten during the build step.
You should only use this option if:
- you don't have permissions to create databases on your SQL server
- you don't plan to run multiple builds in parallel that use the same temporary database. You must use a separate database for each build definition.
If you don't use this option, SQL CI will create a temporary database by default.
/temporaryDatabaseUserName (optional)
The username for SQL authentication.
Example
/temporaryDatabaseUserName=sa
If you don't specify this switch, Windows authentication is used by default.
/temporaryDatabasePassword (optional)
The password for SQL authentication.
Example
/temporaryDatabasePassword=password
This switch is required if you're using the /temporaryDatabaseUserName switch.
/outputFolder (optional)
The output folder path.
Example
/outputFolder=c:\temp
If you don't specify this switch, the current working directory is used by default. Make sure you have permission to create new files and folders in this directory.
/additionalCompareArgs (optional)
Specifies additional command line switches to pass through to SQL Compare.
For example, to abort an operation when there are changes that might result in data loss, pass through the /AbortOnWarnings switch like this:
Example
/additionalCompareArgs="/AbortOnWarnings:Medium"
The switch values should always be surrounded by double quotes.
SQL CI already uses SQL Compare's /Options switch with its own set of default values. To include additional options, use the following syntax:
Example
/additionalCompareArgs="/Options:DoNotOutputCommentHeader,ForceColumnOrder"
The option values are not case sensitive.
You can also use aliases if you prefer shorthand versions of the options. For details of the alias for each option, see Options used in the command line.
Example
/additionalCompareArgs="/Options:nc,f"
To exclude options, prefix the option with a minus sign. For example:
Example
/additionalCompareArgs="/Options:-IncludeDependencies,-IgnoreWhiteSpace"
The default options (with aliases in brackets) used for data comparison are:
- DecryptPost2KEncryptedObjects (dp2k)
- IgnoreFillFactor (if)
- IgnoreWhiteSpace (iw)
- IncludeDependencies (incd)
- IgnoreFileGroups (ifg)
- IgnoreUserProperties (iup)
- IgnoreWithElementOrder (iweo)
- IgnoreDatabaseAndServerName (idsn)
- UseCompatibilityLevel (ucl)
You can include some options and exclude others. For example:
Example
/additionalCompareArgs="/Options:IgnoreConstraintNames,NoTransactions,-IgnoreFillFactor"
If the switch value contains double quotes, you need to escape them with a backslash. For example, if the switch value is /TIL:"READ COMMITTED", use the following syntax:
Example
/additionalCompareArgs="/TIL:\"READ COMMITTED\""
If you're running sqlCI.exe in PowerShell, you must also insert the backtick character (ASCII 96) to escape each double quote. For example:
/additionalCompareArgs="/TIL:\`"READ COMMITTED\`""
For more information, see Windows PowerShell: about_Escape_Characters (TechNet article).
If you don't insert the correct escape characters, the switch value will be invalid.
For more information about SQL Compare command line options and switches, see Using the command line (SQL Compare Documentation).
/licenseSerialKey (optional)
To activate the DLM Automation Suite license on the machine the build agent is running on, enter the serial number.
Example
/licenseSerialKey=123-456-789012-ABCD
For information on finding your DLM Automation Suite serial number, see Legacy licensing. If you don't enter a serial number, a 28 day free trial of the DLM Automation Suite will start automatically.
If you have multiple serial numbers, separate them using commas without spaces.
Example
/licenseSerialKey=123-456-789012-ABCD,321-456-987654-DCBA
/dlmDashboardHost (optional)
If you're using Redgate's DLM Dashboard, enter the name or IP address of the machine hosting DLM Dashboard. If it's running on the same machine, you can use localhost.
Example
/dlmDashboardHost=localhost
When you run the build step, SQL CI sends information about the schema to DLM Dashboard. Once you deploy the changes (for example, by running the sync step), DLM Dashboard:
- recognizes the deployed schema from the information SQL CI sent during the build step
- adds the schema to its list of recognized schemas, with the name <packageId-packageVersion>, for example, WidgetShop1.0
- labels the schema with the DLM Automation icon
- labels the schema as an update
, not drift 
The documentation is stored in db > docs > main.html in the NuGet package. If you're using DLM Dashboard version 1.6.3 or later and have set up DLM Dashboard integration, you can also view the documentation directly from DLM Dashboard. See SQL CI integration (DLM Dashboard documentation).
For help understanding SQL Doc documentation, see What's in the documentation?
/dlmDashboardPort (optional)
The port number for Redgate's DLM Dashboard.
This switch is required if you're using the /dlmDashboardHost option, and you're running DLM Dashboard on a port other than the default 19528. Ignore this switch if you're using the default port.
Example
/dlmDashboardPort=8080
/includeDocs (optional)
Include database documentation in the NuGet package.
Including database documentation will increase the size of the NuGet package and the time it takes to build, particularly for large databases.
Specify /includeDocs to include SQL Doc database documentation in the NuGet package. This switch requires no value.
The documentation is stored in db > docs > main.html in the NuGet package. For help understanding SQL Doc documentation, see What's in the documentation?
