Creating database packages with MSBuild
Published 07 August 2013
If you are not using TeamCity, you can create database packages using a Redgate MSBuild script. To use the script, you must have:
- a SQL Developer Bundle or SQL Toolbelt license
- SQL CI from the DLM Automation Suite (previously called the SQL Automation Pack)
Example: packaging WidgetDev
In the following example, we will run the Redgate MSBuild script to package our WidgetDev database for deployment.
For more information on using MSBuild, see: MSBuild reference (Microsoft documentation).
The example has three steps:
- Downloading and unzipping sqlCI.zip
- Editing the sqlCI.targets file
- Running MSBuild
1. Downloading and unzipping sqlCI.zip
- Download the build script from the Redgate website:
- Unzip sqlCI.zip to a location on your computer.In this example, we are using D:\Database Packages.
sqlCI.zip contains the files required to create database packages. To specify the database you want to package, and where you want the package to be created, you need to edit the sqlCI.targets XML file.
2. Editing the sqlCI.targets file
When you edit sqlCI.targets, you must specify a value for at least the following XML elements:
<scriptsfolder>
The location of your database (scripts) folder.
In this example, we have checked out our database from source control to a local working folder:<scriptsfolder>D:\Databases\WidgetDev</scriptsfolder>
For information about scripts folders and source control, see Working with scripts folders (SQL Compare documentation).
- <generateCreationScript>
Specify<generateCreationScript>true</generateCreationScript>
to generate a SQL script to create the database. The creation script is included in the package. <packageVersion>
The version number for the database package.
In this example, our database is at version 1.0, so we specify<packageVersion>1.0</packageVersion>
If we specify a version number, we need to update sqlCI.targets when we want to create a package with a different version number.
If you want to run the build script from a continuous integration server, you can specify a variable. For example,
$(build_number)
for TeamCity, or$(CCNetLabel)
for CruiseControl.NET.- <packageId>
The name (or ID) for the database package.
In this example, we specify<packageId>WidgetDevDatabase</packageId>
If you specify a value for only these four elements, when you run MSBuild the package will be created in the folder where you unzipped sqlCI.zip.
In our example, we want to publish the package to a feed. To do this, we also specify values for:
<packageRepository>
The URL of your package feed.
In this example, we specify<packageRepository>http://localhost:62759/nuget</packageRepository>
For information on package feeds, see: Working with package feeds.
- <apiKeyForPackagePublish>
The API key for your package feed.
In this example, we specify<apiKeyForPackagePublish>apikey</apiKeyForPackagePublish>
3. Running MSBuild
Now we have specified the required values in sqlCI.targets, we can run MSBuild to package our database.
You can run MSBuild from a continuous integration server (for example Team Foundation Server, CruiseControl.NET, or Jenkins), or from the command line.
To run MSBuild from the command line:
- In Windows Start menu, click All Programs.
- In the Microsoft Visual Studio folder, under Visual Studio Tools, click to run the Visual Studio Command Prompt.
- From the command prompt window, navigate to the folder where you unzipped sqlCI.zip.
In our example, this is D:\Database Packages. - Run
msbuild
.
MSBuild runs, creates the package, and publishes it to the package feed.
Deploying the package
To deploy the package we have created, see Configuring a database package step.