How to automatically track database schema changes

Last modified: March 28, 2025

It’s known that monitoring and managing database schema changes is crucial for business. That’s why you should have efficient tools to track these modifications to save time and avoid errors.

In this topic, you’ll learn how to trace database schema changes automatically with the help of PowerShell scripts.

How to set up automatic database schema changes tracking

The process includes several steps.

1. Create the .txt configuration file.

2. Write the PowerShell script to create a scripts folder.

3. Prepare another PowerShell script that launches the comparison process.

Let’s review each step in more detail.

Create the .txt configuration file

First, you need to create the configuration file that contains such data. In any text editor application, enter the server connection, database name, username, port, and password in a single line separated by commas.

User, Database, Server, Port, Password

In the provided example:

User is the username you can use to establish the connection to the server.
Database is the name of the database you want to connect.
Server is the name of the required server.
Port is the port number of the server.
Password is the password.

Write the PowerShell script to create a scripts folder

After you have created the configuration file, it’s required to write your custom script that starts dbForge Schema Compare for MySQL and creates a scripts folder.
You can run this script manually, schedule its execution, or integrate it into your CI workflow. It is an example of the script:

Click to open the PowerShell script example

#region Variables

$rootFolder = "D:\Monitor\"
$databasesTxtPath = "D:\Monitor\Databases.txt"
$diffToolLocation = "C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"

#endregion

foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
   # Read the connection parameters for the current database from the configuration file
   $userName = ($line -split ",")[0]
   $database = ($line -split ",")[1]
   $server = ($line -split ",")[2]
   $port = ($line -split ",")[3]
   $password = ($line -split ",")[4]
    
   $BaselineLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "BaseLine") 
    
   $srvCleanName = ($server -replace "\\", "")
   $currentScriptsFolder = Join-Path $BaselineLocation "$srvCleanName.$database"     
        
  # Create a database connection
$connectionString = "User ID=$userName;Server=$server;Port=$port;Password=$password;"
$TargetConnectionString = "User ID=$userName;Database=$database;Server=$server;Port=$port;Password=$password;"      
    
# Test the database connection
  Write-Host "Testing the database connection..."
# $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
     try {
           $connection.Open()
           Write-Host "Connection successful"
         } 
   catch { Write-Host "Connection failed: $($_.Exception.Message)" } 
 finally { $connection.Close() }
 
      
   Write-Host "Creating a Scripts Folder: Server $server; Database: $database; Port: $port"
      
   # Create a Scripts Folder =
 Start-Process -FilePath $diffToolLocation "/scriptsfolder /connection:`"User ID=$userName;Host=$server;password=$password`" /database:$database  /path:`"$currentScriptsFolder`" /includedata:No /clearfolder:Yes" -PassThru -Wait -windowstyle hidden                                                                                  

}

Prepare another PowerShell script that launches the comparison process

The last thing is to prepare the second PowerShell script that initiates the comparison process between the created snapshot and a target database. Just like in the previous step, you can execute the script manually, set a schedule for its execution, or incorporate it into your CI workflow. It is an example of the script:

Click to open the PowerShell script example

#region Variables

#[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$rootFolder = "D:\Monitor\"
$databasesTxtPath = "D:\Monitor\Databases.txt"
$diffToolLocation = "C:\Program Files\Devart\dbForge Compare Bundle for MySQL Standard\dbForge Schema Compare for MySQL\schemacompare.com"

#endregion

foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
   # Read the connection parameters for the current database from the configuration file
   $userName = ($line -split ",")[0]
   $database = ($line -split ",")[1]
   $server = ($line -split ",")[2]
   $port = ($line -split ",")[3]
   $password = ($line -split ",")[4]
   $today = (Get-Date -Format "dd-MM-yyyy_HH_MM_ss")
   $BaselineLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "BaseLine") 
   $DiffScriptsFolderLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffScriptsFolder") 
   $ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffReports") 
   $logsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Logs")
   $srvCleanName = ($server -replace "\\", "")
   $currentScriptsFolder = Join-Path $BaselineLocation "company"
   $currentReportFile = Join-Path $ReportsLocation "$srvCleanName.$database.$today"
   $logName = Join-Path $logsLocation "$srvCleanName.$database.$today.txt"
   $diffScriptFolder = Join-Path $DiffScriptsFolderLocation "$srvCleanName.$database.$today"
                    
 Write-Host "Server: $server; Database: $database; Port: $port"
  
# Create a database connection
      $connectionString = "User ID=$userName;Server=$server;Port=$port;Password=$password;"
      $TargetConnectionString = "User ID=$userName;Database=$database;Server=$server;Port=$port;Password=$password;"      
    
# Test the database connection
   Write-Host "Testing the database connection..."
   $connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
     try {
           $connection.Open()
           Write-Host "Connection successful"
         } 
   catch { Write-Host "Connection failed: $($_.Exception.Message)" } 
 finally { $connection.Close() }
 
      
   # Log information about checking the database 
   New-Item -ItemType File -Force -Path $logName
   
      
   # Compare     
$process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source scriptsfolder:$currentScriptsFolder /target connection:`"$TargetConnectionString`" /report:`"$currentReportFile`" /reportformat:html /log:`"$logName`"" -PassThru -Wait -windowstyle hidden
            
         
   # Return exit code 100 in case the databases are identical
  if ($process.ExitCode -eq 100) {
     Add-Content -Path $logName -Value "The databases are identical"
      #remove the newly created report, since no differences are detected
      Remove-Item -Path $currentReportFile".html" -Force:$true -Confirm:$false
      continue
   }
   else {
      Add-Content -Path $logName -Value "The databases are different"
  # Create a Scripts Folder      =
Start-Process -FilePath $diffToolLocation "/scriptsfolder /connection:`"User ID=$userName;Host=$server;password=$password`" /database:$database  /path:`"$DiffScriptsFolderLocation`" /includedata:No /clearfolder:Yes" -PassThru -Wait -windowstyle hidden                                                                                  
}
}

If the script completes execution and there are any schema changes, a new scripts folder will be created with the current date. Along with the scripts folder, a report and a log file will be generated and added to the folders, which you have specified in the PowerShell script. Thus, this method helps you track changes and analyze them.

In case, there are no differences identified, the exit code 100 is returned. The scripts folder and report are not created.

If you want to delve deeper into this process, refer to How to Automatically Track Database Schema Changes With PowerShell Scripts. In this article, you will find a detailed description with the work example.

Was this page helpful?