Automatically script SQL Server table data and object schema directly to source control

Imagine a scenario where you want to get your database into source control quickly and easily, including all schema objects and data from certain code tables that won’t change aka Static data. Then, once you have ported your database successfully to source control, to be able to update the repository nightly with any and all changed objects. In this way, you’ve fully and automatically source-controlled your database, without having to worry about direct integration, check ins, check outs etc. essentially providing much of the “gain” of database source control integration, with little of the “pain”. This article will describe how to build this “poor man’s” SQL database source control integration system using a 3rd party tool, ApexSQL Script

ApexSQL Script, is a scripting tool for SQL databases that supports the Subversion, Git, Team Foundation Server, Mercurial, and Perforce source control systems.

ApexSQL Script provides the ability to script SQL Server table data and structure, or just a few of the desired objects from it, to any of the given source control systems.

Ideally, only changes should be scripted and committed to the repository. The good news is that ApexSQL Script is fully capable of committing just the changes (vs all objects) as well as a newly created objects. However, deleted objects in a database will not be automatically removed from the repository, and they should be removed manually from the repository.

Note: To implement a system that removes deleted objects as well, we suggest using ApexSQL Diff

Regardless of the lack of support for deleted objects, ApexSQL Script can still be used to complete this use case, and this article will help illustrate some of the functionality of the tool, both in GUI and CLI

In this example we are going to script a database to source control including

  1. All schema objects
  2. Data for static tables e.g. zip codes, countries etc. that won’t change over time

The source control system we will use is Mercurial, particularly, we’ll use Mercurial repository hosted on Bitbucket

Then we’ll show how to automate scripting to source control so that this job can run unattended, on a schedule to periodically update the schema objects and add any new ones, but leave the static data unchanged

Description

  1. Once the application had started, in the New project form, connect to the server and select the database from the list presented upon the server connection. Click the Open button:

  2. By switching the View to Structure, check the desired schema objects and only these will be subjected to the process of committing to source control:

  3. By changing the View to Data, choose tables with data that won’t change aka Static data:

  4. Save the project using the Save command from the main toolbar. For the purpose of the article we’ll save it as MyDatabase.axsc:

  5. When the desired objects are selected, click the Script button, in the Home tab, the main form. This will start the Scripting wizard:

    /wp-content/uploads/2015/02/word-image136.png

  6. In the Scripting mode step, set which scripting mode and the output type is needed. Proceed by clicking the Next button:

    /wp-content/uploads/2015/02/word-image137.png

  7. In the next step, un-check the Include dependent database objects check-box, if you do not want to include the dependent objects in the scripting process:

    /wp-content/uploads/2015/02/word-image138.png

  8. In the last step of the Scripting wizard, in the Output file step, the source control settings are presented by choosing the Create and commit to source control option, from the Action drop-down menu, and by clicking the Edit button:

  9. The two-step Source control setup will present. In the first step, the desired source control system should be selected and in the second, the Source control credentials, as well as the repository and working folder needs to be provided in order to proceed:

  10. Once all the fields are filled out with the required information’s, set the application to Script each object into an individual file by changing the Granularity option under the Script file tab:

  11. In the final step, click Create to initiate the committing process:

    Setting up the CLI task

    To automate this to run unattended on a schedule, we’ll create a .BAT file. The goal of this automation is to update existing object schemas, which may have changed, and also add any new objects.

    Note: Objects that may have been deleted will have to be removed from the source control repository manually

    When automating though we are going to make a few important changes:

    1. Rather than scripting only the selected objects, we will configure the batch to script all objects. That way, we ensure any new objects to participate in the job and to be automatically scripted to source control
    2. We will not re-script SQL Server table data e.g. static data, since this data is by definition static, and won’t change
    3. We will supply a dynamically generated comment for each upload with date-time stamp
    4. We will supply a dynamically generated label for each upload with date-time stamp

    First, to simply re-run this project via the CLI, you can use this script:

    ApexSQLScript.com
    /pf:MyDatabase.axsc # loading a project file
    /v # enabling verbose
    /m:structure # selecting only structure for scripting
    /sctype:mercurial # specifies source control system
    /scsrv:”https:user@bitbucket.org
    /user/mydatabase”
    # specifies the path to repository
    /scusr:user # specifies the username for source control
    /scpwd:##### # specifies the password for source control
    /scprj:$ # specifies the project path
    /sccmt:”Committed using ApexSQL Script %DATE% %TIME%” # adds comment with timestamp for committed files

    By running the following command, the result is as follows:

    ApexSQL Script 2016.01, (C)2016 ApexSQL LLC
    Loading project…
    Loading project MyDatabase.axsc
    Set scripting mode:both
    Script each object to an individual file

    Creating script for Tables: Culture
    Creating script for Tables: AddressType
    Scripting data for Table: Culture
    Scripting data for Table: AddressType
    Script processing to source control…
    File(s) checked in to Mercurial
    Labeling ‘ApexSQLSCriptLabel_Wed_03/23/2016_12_57_24.01’…OK

    In case everything finished without errors, the Files checked in to Mercurial message will be shown in the last line of verbose along with the label name (including the timestamp) specified in the CLI command.

  12. Scripting results can be checked directly in the repository:

    Note that any SQL Server table data scripted in this process will be committed as Static data. Also, comments include date and timestamp as specified in the CLI command. Label will be available on the repository:

    To run this script to pick up only changed objects, including any new ones that have been added but avoid re-scripting static data, use this example:

    ApexSQLScript.com
    /pf:MyDatabase.axsc # loading a project file
    /v # verbose console output
    /m:both # includes structure and data for scripting
    /sctype:mercurial # specifies source control system
    /scsrv:”https:u@bitbucket.org
    /u/mydatabase”
    # specifies the path to repository
    /scusr:user # specifies the username for source control
    /scpwd:##### # specifies the password for source control
    /scprj:$ # specifies the project path ($ is a repository root)
    /scwfd:C:\WF # working folder on a local machine
    /in # script each object as a separate file
    /sclbl:”ApexSQLSCriptLabel_
    %DATE: =_%_%TIME::=_%”
    # specify a label including the timestamp
    /sccmt:”ApexSQL Script commit %DATE% %TIME%” # Commit message with the timestamp
    /nfsh # excludes object headers from scripting

    Note that the dynamic date stamped comment (/sccmt:”Committed using ApexSQL Script %DATE% %TIME%”) and label (/sclbl:”ApexSQLSCriptLabel_%DATE: =_%%TIME::=_%”) are supplied and that these variables are outputted as a part of the result.

    Also note that the /nfsh (aka /no_format_scr_header) will need to be used to suppress the header, which will cause all objects to be scripted, regardless of whether they were changed or not. This issue will be resolved in the next version.

Click here to see a full documentation of ApexSQL Script CLI

To later re-create the schema objects and static data from source control, back into an existing database, we recommend that you use ApexSQL Build.

FAQs

Q: Will a new version of the object be created in source control each time, even if it didn’t change, or will the source control system be smart enough to detect only changed objects?

A: No, a new version of an object will not be created if there are no changes. This is done with adding the /nfsh switch.

Q: What happens if I re-run the project with the same label? Will it cause an error from the underlying source control system?

A: The task will not finish successfully and the following error appears in verbose:

Script processing to source control…
Labeling ‘Label1’…Error:
There was an error when labeling scripts folder. Source control error: Label ‘Label1’ already exists. Specify other name

March 28, 2016