How to pull version controlled SQL Server database static data from a repository

Prelude

In the article “Understanding SQL Server database static data and how it fits into Database lifecycle management” we covered what is static data in general and numerous reasons why it needs to be version controlled.

In the article “How to link and initially commit SQL Server database static data”, we covered how to get static data into source control for the first time.

In the next article, we will discuss how to work with static data, checking it out, making changes, and checking it back into the repository.

Challenge

In this article, we are going to cover the challenge of how to pull version controlled static data out of source control and into a local database, for QA or staging purposes.

Solution

In order to pull static data out of a repository and apply it on a version controlled database, we’ll use ApexSQL Source Control. This is a 3^rd party tool that not only supports all popular source control systems but also treats static data like any other database object. We will also use the AdventureWorks2014 database linked to a Git repository hosted on BitBucket.

First of all, we will have to link and commit the static data to the repository by following the steps in the article “How to link and initially commit SQL Server database static data”. After completing this process, the Action center will display no objects for the user that linked static data, as everything should be synced.

For the next user who links the database to the same repository, the Action center will display static data tables as objects that can be pulled from the repository. For the purposes of this article, we will continue working with the BusinessEntity, Currency and Department tables as static data.

However, if we try to check these items at this point, we will be prompted that static data has to be linked first.

There are two ways of doing that:

• The first one is through the Action center, by clicking the related link for each one of the tables. Although this is the easier of the two, it can be more time consuming and prone to error if we are managing a big number of static data tables.

  

• The second is by using the Static data form, which can be accessed through the Object explorer or by the keyboard shortcut Ctrl+Alt+S. This form not only allows all tables to be linked simultaneously, but also provides a clear view of which tables are linked as static data (checked).

  

  

Once this last step has been completed, we have successfully pulled version controlled static data from the repository and into the local database. Users can now start working with static data in the same way they do for any other database objects. However, the process of editing, tracking changes and comparing static data is out of the scope of this article and will be covered in a subsequent one.