Version control an existing database - getting started

In this tutorial, we will demonstrate how to version control an existing SQL Server database. We will use the AdventureWorks sample database (that has been restored on a local instance of SQL Server) as well as Github Desktop to add scripted database files to the local GIT repository.

New project settings

From SSMS (SQL Server Management Studio), click Tools - DB Version Control. You will be presented with Recent Projects screen. Click New project button.

The Settings tab will appear:

  • Choose a location where scripted database files will be stored (Folder on disk)
  • Specify connection details for the database that will be version controlled in Source connection fields group
  • Specify connection details for a temporary database in Create database connection section - this database is created from scripted database files and improves the comparison performance. By default, the tool assumes that a temporary database will be created on the same database server as the database that will be version controlled. Important! Make sure that you have CREATE DATABASE permissions on the database server.

There are additional settings (such as Options and Variables) that can be configured but we will skip them for simplicity.

Click Next on the bottom right of the screen.

Schema objects

In the Objects Filter tab, you will be presented with a list of database schema objects grouped by their type (e.g. tables, views, stored procedures, etc.). By default, all schema objects are included but you can choose to exclude objects that should not be versioned.

For simplicity, we will include all database schema objects. Click Next at the bottom right of the screen.

Static data

In the Static Data Filter tab, you will be presented with a list of data tables that are available in the database. DB Version Control allows including data tables to version their data (that is, actual values in the table's rows and columns!).

By default, no data tables are versioned automatically and for simplicity, we will not choose any tables to version control in this tutorial.

Click Next at the bottom right of the screen.

Initialize

At this point, we specified which database to version control, which schema objects and static data to include and we are ready to initialize the project.

We will cover multi-environment database development process in another tutorial.

To continue, click Initialize button.

At this moment, the tool will create a new temporary database and will try to script all our selected schema objects and static data.

In our case, we got an error:

Such errors may appear due to the configuration of SQL Server or because some objects in the database are invalid (e.g. a view references a table that no longer exists).

In this particular case, the SQL Server (where the AdventureWorks database has been restored) does not have full-text indexing functionality enabled. There are two ways to fix this issue - to update SQL Server setup to include full-text indexing functionality or to exclude [dbo].[uspSearchCandidateResumes] stored procedure from being versioned.

The simplest option would be to exclude an object from being versioned. To do that, we go back to Objects tab and under Stored procedure group choose Exclude unselected only option and untick [dbo].[uspSearchCandidateResumes]. We also exclude all Full text catalog objects.

Now, we can go back to the Changes tab and click Initialize again. If everything goes well, you will get Database scripted message.

So, what happened when we click Initialize? The project's configuration, database schema objects, and static data were scripted into the folder we specified. To view the files, click on the Structure tab (clicking on each file previews the contents of that file) or navigate to the folder directly on your machine.

Project's structure

You will see different directories and files:

  • Database.sqlproj file
  • Migrations directory
  • A directory per object type (e.g. Tables, Views, Stored procedures, etc.)

Database.sqlproj is the file that can be opened within Visual Studio and the project included with your application's source code.

Migrations directory contains Baseline directory, which contains two SQL files:

  • !Create_database.sql
  • Baseline.sql

The Create database file contains SQL commands to create a new empty database. This file can be modified to specify options for how a database should be created, e.g.:

  • The location of data and log files
  • Recovery mode, compatibility level, collation, etc.

The Baseline file contains SQL commands to populate an empty database with all the database objects that we included in the project's configuration.

The other directories (per object type, e.g. tables) have each object scripted out into a separate file for change tracking.

Add to the version control system

The scripted database files can now be added to the version control system of your choice.

In this tutorial, we use Github Desktop and click File - New repository and fill in the required fields.

Once a local repository has been created, the initial commit was done automatically (by Github Desktop). To view our commit, choose the History tab.

Summary

In this tutorial, we showed how to get started with DB Version Control tool:

  • Creating and configuring a new project
  • Including/excluding schema objects
  • Handling possible database-related errors
  • Viewing scripted database files
  • Adding scripted database files to the version control system

To learn more, check additional documentation on:

In the next tutorial, we will show how to make changes to the database and keep them in version control.

Leave a comment