Deployment Manager 2

Creating and publishing database packages using the command line

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

This is particularly useful if you want to script the publishing of a package.

 

 

 


Didn't find what you were looking for?