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

Imagine a scenario where there is a need to get a 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 a database is ported successfully to source control, to be able to update the repository nightly with any and all changed objects. In this way, a database will be fully and automatically source-controlled, 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 3^rd party tool, ApexSQL Script.

ApexSQL Script, is a scripting tool for SQL databases that supports the Subversion, Git, Mercurial, Perforce,Subversion and Team Foundation Server, 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 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, a database will be scripted 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 that will be used is Git, particularly, the Git repository hosted on Bitbucket.

Then, automation of the scripting process to source control will be shown, 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 is started, in the New project window, connect to a SQL Server and select a database from the list presented upon the server connection. Click the Load button:

   

2. Upon loading a database with its objects and data, 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 button from the main toolbar. For the purpose of this article, the project will be named as MyDatabase.axsc:

   

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

   

6. In the Scripting mode step, set which scripting mode will be used. For the purpose of this article, the Structure and data scripting mode will be used:

   

7. Under the Output type step, select Source control:

   

8. In the next step, un-check the Include dependent database objects check-box, if there is no need to include the dependent objects in the scripting process:

   

9. In the last step of the Script wizard, in the Source control options step, the source control settings should be provided with a click on the Setup button:

   

10. The two-step Source control wizard will be shown. 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 project needs to be provided in order to proceed:

    

    

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

    

Setting up the CLI task

To automate this to run unattended on a schedule, a .BAT file will be created. 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, a few important changes need to be made:

1. Rather than scripting only the selected objects, the batch file will be configured 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. SQL Server table data e.g. static data will not be re-scripted, since this data is by definition – static, and won’t change
3. A dynamically generated comment for each upload with date-time stamp will be supplied
4. A dynamically generated label for each upload with date-time stamp will be supplied

First, to simply re-run this project via the CLI, the following script cn=an be used:

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

ApexSQL Script 2019.01, (C)2019 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 Git
Labeling ‘ApexSQLSCriptLabel_Wed_12/25/2019_12_57_24.01’…OK

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

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:

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

Also, check out the article on how to create the automation script, without the need to create manually.

To later re-create the schema objects and static data from source control, back into an existing database, it is recommend to 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