How to automatically track database schema changes

Last modified: July 4, 2024

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 if you want 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. They are the following:

1. Create the .txt configuration file.

2. Write the PowerShell script to create a database snapshot.

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

Let’s review each step in more detail.

Create the .txt configuration file

First of all, you need to create the configuration file that contains such data:

Server name
Databases you want to compare
Authentication type
Login and password

Write the PowerShell script to create a database snapshot

After you have created the configuration file, it’s required to write your custom script that starts dbForge Schema Compare for SQL Server, captures a database snapshot, and puts it into the specified folder.
You can run this script manually, schedule its execution, or integrate it into your CI workflow. This is an example of the script:

#region Variables

$rootFolder = "[folder]"
$databasesTxtPath = "[path_to_text_file]"
#Declare $diffToolLocation variable for dbForge Studio for SQL Server
$diffToolLocation = "C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.com"
#Declare $diffToolLocation variable for dbForge Studio for SQL Server
#$diffToolLocation = "C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"

#endregion

foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
   # Read the connection parameters for the current database from the configuration file
   $server = ($line -split ",")[0]
   $database = ($line -split ",")[1]
   $isWindowsAuthentication = ($line -split ",")[2]
   $userName = ($line -split ",")[3]
   $password = ($line -split ",")[4]
    
   $BaselineLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "BaseLine") 
    
   $srvCleanName = ($server -replace "\\", "")
   $currentSnapshotFile = Join-Path $BaselineLocation "$srvCleanName.$database.snap"    
    
        
    # Create database connection
   if ($isWindowsAuthentication -eq 'True') {
      $connectionString = "Server=$server;Database=$database;Integrated Security=True;"       
                                 }
   else {
      $connectionString = "Server=$server;Database=$database;User ID=$userName;Password=$password;"
        }


   # Test database connection
   Write-Host "Testing the database connection..."
  $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
     try {
           $connection.Open()
           Write-Host "Connection successful"
         } 
   catch { Write-Host "Connection failed: $($_.Exception.Message)" } 
 finally { $connection.Close() }
   
      
   Write-Host "Creating a snapshot for the Server: $server; Database: $database"
      
   # Create a snapshot      
    if ($isWindowsAuthentication -eq 'True') {
 Start-Process -FilePath $diffToolLocation "/snapshot /connection:`"Data Source=$server;Initial Catalog=master;Integrated Security=True;User ID=$userName`" /database:$database  /file:`"$currentSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden
                                             }                                            
  else {  
 Start-Process -FilePath $diffToolLocation "/snapshot /connection:`"Data Source=$server;Initial Catalog=master;Integrated Security=False;User ID=$userName`" /database:$database /password:$password /file:`"$currentSnapshotFile`" /compress:No" -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. This is an example of the script:

#region Variables

$rootFolder = "[folder]"
$databasesTxtPath = "[path_to_txt_file"
#Declare $diffToolLocation variable for dbForge Studio for SQL Server
#$diffToolLocation = "C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.com"
#Declare $diffToolLocation variable for dbForge Studio for SQL Server
$diffToolLocation = "C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"

#endregion


foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
   # Read the connection parameters for the current database from the configuration file
   $server = ($line -split ",")[0]
   $database = ($line -split ",")[1]
   $isWindowsAuthentication = ($line -split ",")[2]
   $userName = ($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") 
   $DiffsnapshotsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffSnapshots") 
   $ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffReports") 
   $logsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Logs") 
   $srvCleanName = ($server -replace "\\", "")
   $currentSnapshotFile = Join-Path $BaselineLocation "$srvCleanName.AW2019Dev.snap"
   $currentReportFile = Join-Path $ReportsLocation "$srvCleanName.$database.$today"
   $logName = Join-Path $logsLocation "$srvCleanName.$database.$today.txt"
   $diffSnapshotFile = Join-Path $DiffsnapshotsLocation "$srvCleanName.$database.$today.snap"
                    

   Write-Host "Server: $server; Database: $database; isWindowsAuthentication: $isWindowsAuthentication"

    
# Create database connection
   if ($isWindowsAuthentication -eq 'True') {
      $connectionString = "Server=$server;Database=$database;Integrated Security=True;"
      $TargetConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;"       
                                 }
   else {
      $connectionString = "Server=$server;Database=$database;User ID=$userName;Password=$password;"
      $TargetConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=False;User ID=$userName;Password=$password;"
        }


   # Test database connection
   Write-Host "Testing the database connection..."
  $connection = New-Object System.Data.SqlClient.SqlConnection($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 snapshot:`"$currentSnapshotFile`" /target connection:`"$TargetConnectionString`" /report:`"$currentReportFile`" /reportformat:html /includeobjects:All /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"
 # Generate a new snapshot in case there are differences detected 
      if ($isWindowsAuthentication -eq 'True') {
 Start-Process -FilePath $diffToolLocation "/snapshot /connection:`"Data Source=$server;Initial Catalog=master;Integrated Security=True;User ID=$userName`" /database:$database  /file:`"$diffSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden
                                             }                                            
  else {  
 Start-Process -FilePath $diffToolLocation "/snapshot /connection:`"Data Source=$server;Initial Catalog=master;Integrated Security=False;User ID=$userName`" /database:$database /password:$password /file:`"$diffSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden
       } 
}
}

If the script completes execution and there are any schema changes, then a new snapshot will be created with the current date. Along with the snapshot, 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 snapshot 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?