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:
  1. Go to the SQL Automation Pack page and download the free trial.
  2. Install the SQL Automation Pack.
  3. Run the Intro to SQL Automation Pack application.
  4. Under SQL CI, click Open Folder.
  5. Copy the contents to a folder on your machine.

  6. 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:

1. Open a command prompt.
2. Change the working folder to the unzipped sqlCI folder.

3. 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.

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.

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.