Three phased approaches to getting your SQL Server database into source control

Challenge

Database development teams show an increasing interest in getting their SQL Server databases into source control. However, depending on the team’s actual needs, their development plan and the level of source control integration required, there are various ways to achieve getting a database under source control.

January 25, 2017

How to commit SQL Server table static data to a source control repository

Challenge

Once a SQL database is committed to a source control repository with all of its objects, the next task is to commit the static data. Static data is non-transactional data from tables that generally don’t change often like postal codes, department names etc. Many teams treat this type of data akin to the database structure itself, by versioning it to track changes and deploying it from source control just like database objects. As data is versioned in source control, managing it can be automated as well.

October 7, 2016

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

March 28, 2016

How to script SQL Server database users with roles

Introduction

A request is received from the development team asking to copy one of the production databases to the DEV SQL Server in order to simulate real time testing scenarios on it. Once the production database backup is restored to the DEV SQL instance, replacing the old copy, the old DEV database users will be replaced with the live ones. But we still need the old DEV database logins for the DEV site connection.

February 5, 2016

How to work with SQL database source control labels

A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.

October 27, 2015

How to automate batch SQL object scripting and committing to source control

Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.

In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.

April 15, 2015

How to combine objects and data from multiple SQL databases in a single SQL script

In some cases a development team may decide to migrate databases between environments by generating a deployment script from multiple SQL databases (development/testing). They can use a tool like ApexSQL Script to create a single script from multiple sources, making it easy to apply to the target environment or the source control. Using this feature, there’s no need to script one SQL database at a time, as multiple data sources are processed simultaneously.

February 24, 2015