How to build a SQL database from source control without dependency problems


One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.

Namely, simply executing the database object’s scripts without taking into account objects’ interrelationships will result in the:

Msg 208, Level 16, State 1

error message and a failed deployment. To fix this issue, the contents of the deployment SQL script need to be reviewed and the individual object scripts need to be rearranged within the deployment script. Doing this manually can be a long and tedious process, not to mention error prone, even if the database that is to be rolled out contains less than 50 objects and is borderline impossible to get right the first time if dependencies of hundreds or thousands of object need to be checked. The good news is, there is a better way to prevent dependency error on database deployment than by checking them manually.

When building a database deployment script from source control:

  1. Make sure that you have the latest versions of the scripts for all of the objects that are to be deployed
  2. Organize the build script in the following order:
    1. Start with user defined types, if any, and follow up with
    2. User defined functions
    3. Tables
    4. Stored procedures
    5. Views
    6. Indexes, etc.

However, although the guidelines mentioned above are a good rule of the thumb, they don’t address scenarios when objects of the same type depend on one another (e.g. a view which depends on a view). In those cases, objects of the same type still need to be ordered manually.

Another solution is to use a 3rd party tool that creates deployment scripts from source control.

ApexSQL Build is a SQL database deployment tool that builds new databases or updates existing ones by packaging SQL scripts, script folders, database snapshots and even scripts under source control into a single deployment SQL script, a C# solution or an executable installer. It allows specifying the objects to be created or updated, ensures error-free deployment, enables executing SQL scripts against multiple SQL Servers simultaneously and more

Before building a SQL database with ApexSQL Build, make sure that an existing or new source control repository has the latest committed changes.

To generate a deployment script without any dependency problems:

  1. Start ApexSQL Build

  2. Select the Build a new database action, from the Build tab:

  3. Select the Database option as an output type:

    Select Database as an output type

  4. Select Source control as the input source and click the Edit button:

    Select Source control as an input source

  5. The Source control setup wizard will be opened and in the first step a source control system should be chosen:

  6. Enter the login information and source control system parameters:

    Learn more about setting up the Source control wizard from here.

  7. Click the Finish button

  8. ApexSQL Build will retrieve all files from the source control system to a local working folder:

    ApexSQL Build retrieves all files from SC system

  9. Click the Next button

  10. Specify the SQL Server connection along with the Database properties for a new database that will be built from the specified source control repository. To copy the database properties from the database on the source control repository, click the Get database properties from the input source button:

    Once everything is set, click the Next button

  11. The list of the objects that will be created is shown. By default, all objects are checked, but if some objects are not needed, uncheck them and they won’t be included in the building process:

  12. Once the Next button is clicked, the list of actions that will be executed is shown in the Action summaries under the Preview step:

  13. Switch to the Dependencies tab and include any dependent objects to ensure the error free build process:

  14. In the next step, add post deployment scripts if there is something that should be executed upon the building process is finished. In this case, two tables, a view, and a procedure will be created after the database is built:

  15. In the last step choose whether to save a copy of generated script locally or not:

  16. Before the Build button is clicked, click the Save button in the bottom-left corner, to create a project file that can be used later for automating the build process

  17. Click the Build button

  18. Once the build process is finished, the Results window will be shown:

Automating the build process from the source control repository with ApexSQL Build CLI

The previously described process can be also run unattended, by creating a project file at the end of the process and using it in the ApexSQL Build’s CLI.

The saved project file can be called from the CLI, along with the optional switches that can provide additional information of the build process:

“ApexSQLBuild.com” # application path
/pr:”MyProject.axbd” # project file path
/v # prints all messages in the console
/f # overwrites existing script files
/out:”Output.txt” # redirects console output to a specified file
@echo ApexSQL Build return code is %errorlevel% >> /out:”Output.txt” # return code upon finished building process

This process can be also run with an option to show the prompt window (attended) for database/source control credentials. Learn more about it from article on this link.

Creating a database from a specific version of DDL scripts stored in a source control system doesn’t have to be a problem. ApexSQL Build generates a script that creates all objects successfully and eliminates all problems with missing dependencies.

April 4, 2013