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:
- If the database has hundreds or thousands of objects, it’s a lot of diving
- 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 front of them
Another solution is to use a 3rd 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 .NET solution or an executable installer. It allows specifying objects that will be created or updated, ensures error-free deployments, enables executing SQL scripts against multiple SQL Servers simultaneously and more.
To generate a deployment script:
- Start ApexSQL Build
- In the Build tab, select the Update an existing database action:
Select T-SQL as an output type:
Select Source control as an input source and click the Edit button:
- Select the source control system where the versioned scripts are stored:
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:
Click the Finish button to retrieve the files from the source control system:
- Click the Next button
- Specify the new database properties
- The list of the objects that will be created is shown. They are all selected by default, and if some of them don’t have to be created, uncheck them:
- In the next window, the list of actions that will be executed is shown. After viewing them, click the Next button:
- In the last step, choose whether to Save script to file or to Open script in editor and click the Build button to create a deployment script:
If you don’t want to execute the script immediately, select Create and write to a file. You will be able to review and modify the created script and execute it afterwards using SQL Server Management Studio
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.
April 4, 2013