Creating and publishing database packages using the command line
Published 07 August 2013
This page describes how to create and publish a database package using the version of SQL CI available in SQL Automation Pack v1.0.4.2 and earlier. If you're using a later version of the SQL Automation Pack, see Using the sqlCI.exe command line in SQL Automation Pack v1.1 and later.
The SQL Automation Pack was replaced by the DLM Automation Suite on 31st March 2015. If you're using a version of SQL CI included in the DLM Automation Suite, use the commands described in Using the sqlCI.exe command line.
You can use the SQLCompare.exe and sqlCI.exe command lines to create and publish database packages. You can then use Deployment Manager to deploy database packages to target SQL servers.
Requirements
You will need:
- A copy of SQL Automation Pack v1.0.4.2 or earlier. To install this:
- Go to the SQL Automation Pack page and download the free trial.
- Install the SQL Automation Pack.
- Run the Intro to SQL Automation Pack application.
- Under SQL CI, click Open Folder.
- Copy the contents to a folder on your machine.
Optionally, license sqlCI.exe by running:
sqlCI.exe --licenseSerialKey=[automation license serial number]
If you don't license sqlCI.exe, it'll start a 2 week trial.
- Access to a SQL server
- A database on the server to package
Create a scripts folder with SQLCompare.exe
To create a temporary scripts folder of your target database, use the SQL Compare command line:
- Open a command prompt.
- Change the working folder to the unzipped sqlCI folder.
Run the following command:
SC\SQLCompare.exe /server1:[server] /database1:[database] /makescripts:temporary_folder
Where:
- [server] is your target database server
- [database] is the name of the database you want to package and publish
This creates a scripts folder of the database in a folder called temporary_folder.
For more information, see Switches used in the SQL Compare command line.
Publish the package with sqlCI.exe
To package and publish the database to Deployment Manager, run the following sqlCI.exe command:
sqlCI.exe --scriptsFolder=temporary_folder --packageId=[package name] --packageVersion=[version number] --packageRepository=[package repository] --apiKeyForPackagePublish=[DM API key] --generateCreationScript
Where:
- --scriptsFolder=temporary_folder tells sqlCI.exe to use the scripts folder as the target to package.
- [package name] is what you want to name the package. Often the name of the database
- [version number] is the version number of the package that will appear in Deployment Manager
- [package repository] is the address of your NuGet package repository. Usually, this is your Deployment Manager URL with /nuget appended to it
- [DM API key] is the API key for your Deployment Manager server if it requires one. Otherwise this can be left blank
- --generateCreationScript generates a SQL creation script
This will package and publish the scripts folder that you created with SQLCompare.exe.
For information, see Using the sqlCI.exe command line in SQL Automation Pack.
Cleaning up
Use the command line to delete the temporary scripts folder you created.
Run the following command:
rd /s /q temporary_folder
Where:
- /s removes the whole directory tree under temporary_folder
- /q sets quiet mode so it doesn't ask for confirmation when removing the directory tree
- temporary_folder sets the folder to delete
This removes the temporary folder and all its subfolders.