How to create a SQL build script from the source control repository

It’s quite common for developer teams to use database object versioning. The creation scripts for every table, view, stored procedure, and other objects in the database are added to a source control system. That way, everything is versioned and the team is safe.

Applying a specific version of a source control system to a database is not a problem and it can be done by using a source control client.

But, what if there is a need to use versioned scripts in another environment, for example when a version from 3 days ago is required for testing. This version is stored in the source control system and a deployment script needs to be generated that will re-create those objects in a new database.

How to create a SQL script from the version stored in the source control system

Dive into the repository, find the needed version for each database object and execute the script against a new database to create the objects. However, with this method 2 major obstacles could be encountered:

1. If the database has hundreds or thousands of objects, it’s a lot of diving
2. If creating a database object that depends on another, non-existent object, an error will be encountered. The solution is to create the parent objects first, and then create the objects that reference them. Again, this would be easy if the database only had a couple of objects and all users have a dependency schema in frontUpdate-an-existing-database1au1 of them

Another solution is to use a 3^rd party tool that creates deployment scripts.

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 scripts under a source control into a single deployment SQL script, a C# solution or an executable installer. It allows specifying objects that will be created or updated, ensures error-free deployments, and more.

To generate a deployment script:

1. Start ApexSQL Build
2. In the Database section, under the Home tab, click the Update button:

   

3. Select SQL as an output type:

   

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

   

5. Select the source control system where the versioned scripts are stored:

   

6. Enter the login information and source control system parameters. Once they are set, select a specific version of the scripts by checking the Get latest or Get by label option:

   

7. Click the Finish button to retrieve the files from the source control system:

   

8. Click the Next button
9. Specify the server name along with the authentication type, and a database that will be updated:

   

10. The list of the objects that will be created is shown in the Update objects step. They are all selected by default, and if some of them don’t have to be created, uncheck them:

    

11. In the next window, the summary of actions that will be executed is shown. After viewing them, click the Next button:

    

12. In the last Output options step, choose whether to Save a copy of the script or to Open script in editor and click the Finish button to create a deployment script:

    

    If the script doesn’t need to be executed immediately, select the Save a copy of the script option. This will allow users to review and modify the created script and execute it afterwards using SQL Server Management Studio.

13. Once the script is built, the following message will be shown:

    

    The created script is ready to be reviewed or modified and executed afterwards, using SQL Server Management Studio.

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.