SQL Server database and scource control

Question

Hi All.

I am looking to get some information on best practices on how to get database code into source control. Please note that the database use pattern is mixed.

• Application related database objects
• Ad-hoc database queries.

The objects within the database consists of views, stored procedures, tables etc.

How do we get the various database objects into source control, script out the objects and load onto source control.

I also understand there are database build tools like visual studio etc. I would like to know how this works with source control.

Thanks in advance.

Answer 1

"Database => Tool => Source Control" is the wrong way round, it should be the opposite.

Create a new "Database project", do a right-mouse click on the project => "Import …" => "Database" and select the existing database, SSDT creates for all objects. See Import into a Database Project
From then all changes are done in the database project and when finished, you deploy the changes to the existing database; for this SSDT generates CREATE & ALTER script.
During this process you can check-in the changes to the source control system.

Answer 2

Hello,

You can use SSDT = "SQL Server Data Tools" to develop your databases; it's a plugin for Visual Studio and so you can use any supported source control software, like TFS, DevOps, SVN, GitHub and so on.

SSDT is free available at Download SQL Server Data Tools (SSDT) for Visual Studio

Answer 3

To add, it is free to use in any context with Visual Studio Community.

Answer 4

Thanks for everyones input, let me describe some typical scenarios.

**

• Scenario 1

**

An existing database, do we script out all objects and place them in source control ?

**

• Scenario 2

**

Working on a new tool/development and database objects needs to be created to support the work. In this case, I am assuming that one would have scripted out the database and place it in source control, do you then create a branch, add your new objects and once development/testing is complete merge it back to the database master branch. What then happens to the other piece of code that is not SQL, lets say C#. Will the database object be bonded together with C# code or they stay separate. I can only assume here they will be separate as the database code can drift away from the application code.

**

• Scenario 3

**

There is an existing database object that needs to be changed and update placed back in source control

In all the scenarios described above, how does SQL server data tools help. The source control here is GIT.