How to Automatically Commit SQL Server Database Schema Changes to the GIT Repository

Want to make git ‘commit’ automatically run every time a database schema is updated? Read more to find out how you can do this with dbForge Schema Compare for SQL Server.

In a perfect world, any schema changes to a database come through a strict review and management process with quite a few database administrators having access to deploying those changes. Unfortunately we do not live in a perfect world. Thus, tracking the history of schema changes is essential for database health. As a DBA, you might want to know the whole history of schema updates, even if the user later chose to “undo” their changes to a database.

In this article, we will demonstrate to you how to configure git-autocommit on every change in the structure of your databases.

How to set up database schema changes git autocommit: Step-by-step guide

Let us first have a look at the overall logic of the process, and then dwell on each step in a more detail.

Scenario

1. Create a scripts folder from the database whose schema changes you want to git-autocommit.
2. Link that scripts folder to a Git repository.
3. Run the PowerShell scripts that initiates the schema comparison between a database and a scripts folder. Here, the functionality of dbForge Schema Compare for SQL Server is used.
4. In case there are no changes detected, no action is taken. However, if there are any schema changes, the scripts folder gets updated.
5. After the scripts folder has been updated, the changes are automatically committed and pushed to the Git repository.

Let us now look at the real-life example, showing to put this into practice.

Worked example

Prerequisites

• A database the schema changes of which you want to autocommit
• dbForge Schema Compare for SQL Server downloaded and installed
• A scripts folder linked to the remote Git repository

PowerShell script

The following script initiates schema comparison, and in case there are schema changes detected, it updates the scripts folder and automatically commits and pushes the changes to the remote Git repository. All you need is to run this script from the command line. You can either create a .ps1 file with the given script and initiate its execution from PowerShell or Command Prompt or paste the script directly to terminal.

$ErrorActionPreference = "SilentlyContinue"

$connectionString = "Data Source=server_name;Initial Catalog=database_name;Integrated Security=False;User ID=user_name; Password=password"
$scriptsfolder = "D:\JordanS\dbForgeSchemaCompareRepository\scriptsfolder_name\"
$diffToolLocation = "C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com"

# Set working directory where a script folder repo is located
Set-Location -Path $scriptsfolder
    
# Switch to branch "main" and update the files in the working directory
git checkout main

# Launch schema comparison between a database and a scripts folder
$process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source connection:`"$connectionString`"  /target scriptsfolder:`"$scriptsfolder`"" -PassThru -Wait -windowstyle hidden

# Exit code 101 means that schema differences have been detected.
if ($process.ExitCode -eq 101) {    

    Write-Host "There are differences between the database and the scripts folder."

    # Synchronize the database and the script folder
    $process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source connection:`"$connectionString`"  /target scriptsfolder:`"$scriptsfolder`" /sync" -PassThru -Wait -windowstyle hidden

    # Add the files or changes to the repository
    git add --all 

    # Commit new files/changes to the local repository
    git commit -m "Script folder is changed" 

    # Push to the remote branch
    git push -u origin main
}
elseif ($process.ExitCode -eq 100){
    Write-Host "Database and scripts folder are identical."
}

Where:

• connectionString – specifies information about a data source and how to connect to it.
• $scriptsfolder – provides a path to the scripts folder.
• $diffToolLocation – specifies a path to the tool to be used for schema comparison.

Note
There are two tools by Devart that you can use for comparing schemas between a database and a scripts folder: Schema Compare for SQL Server, which we have already mentioned earlier, and a all-in-one IDE containing a bunch of useful tools for working with SQL Server – dbForge Studio for SQL Server.

Suppose, we have the AdventureWorks2019 database scripts folder that is linked to our JordanSandersRepo GitLab repository.

Let us save the scripts as UpdateDiff.ps1 and run it from the command line. The connection string for it will look like as follows:

$ErrorActionPreference = "SilentlyContinue"

$connectionString = "Data Source=JordanS\SQLSERVER2019;Initial Catalog=AdventureWorks2019;Integrated Security=False;User ID=JordanS; Password=JordY"
$scriptsfolder = "D:\JordanS\AdventureWorks2019\"
$diffToolLocation = "C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com"

Note
You need to provide a full path to the scripts folder.

As you can see, our database and the scripts folder are different, and the exit code of the operation is 0, which means it was successful.

Let us now look at our Git repository.

As you can clearly see, a new commit containing the schema changes has been made to the repository.

Let us run the script again. Our database and the scripts folder are now supposedly identical.

The operation returns the 100 exit code, which means that there are no differences between the database and the scripts folder.

How to automate and schedule database comparison and synchronization with the version-controlled scripts folder

You can schedule and automate the comparison and synchronization task described above with the help of dbForge Schema Compare for SQL Server (or dbForge Studio for SQL Server) and Windows Task Scheduler.

Scheduling database comparison and synchronization includes the following steps:

• Create a .ps1 file containing the PowerShell script to initiate schema comparison and automatically commit and push the changes to the remote Git repository.
• Create a .bat file to initiate that script.
• Create a synchronization task using Windows Task Scheduler to call that .bat file.

As we have already created a .ps1 file, let us move to the step 2.

To create a .bat file:

1. Open any 3-rd part text editor, for example, a Notepad or Notepad++.
2. Enter the following command to it: PowerShell.exe -file UpdateDiff.ps1
3. Save the file with .bat extension.
4. Place the newly created .bat file to the same folder with your .ps1 file.

Now, all you need is to schedule that .bat file execution with any task scheduler tool, for example, Windows Task Scheduler.

To schedule the .bat file execution:

1. Open the Control Panel > Administrative Tools and select Task Scheduler.

2. In the Task Scheduler window that opens, navigate to the Actions pane and click Create Basic Task to create a scheduled task.

3. In the Create Basic Task Wizard window that opens, specify the name and description of the task and click Next.

4. On the Trigger tab, choose when to launch the task and then click Next.

5. On the Action tab, click Start a program to schedule a program to start automatically and then click Next.

6. On the Start a Program subtab, click Browse to select the .bat file you have created earlier and then click Next.

7. On the Finish tab, verify the settings and click Finish.

The task will be displayed in the Active Tasks section.

Conclusion

Devart products give you the power to automate your database routines. In this article, we provided a detailed walkthrough on how you can automate and schedule your schema comparison tasks. All you need is dbForge Schema Compare tool, a PowerShell script, and a .bat file. Couple of minutes of preparations, and you will free up a lot of time time for more important tasks.

How to get this functionality

To give our customers more choice, the dbForge Schema Compare for SQL Server functionality can be acquired as part of three different toolkits:

• dbForge Compare Bundle for SQL Server (a pack of two essential compare tools)
• dbForge SQL Tools (a pack of 15 advanced tools for DB development, management, and administration)
• dbForge Studio for SQL Server (an all-in-one IDE designed to cover all the possible database-related tasks).

Just choose the tools pack that best meets your project requirements, download it from our website, and start a free 30-day trial to evaluate the functionality.