How to Automatically Track Database Schema Changes With PowerShell Scripts

November 11th, 2021

In the article, you will find a detailed guide on how to automatically monitor a SQL Server database for schema changes and automatically create a report and log file. The examples of scripts to set up the process are provided.

Automation of database schema changes tracking increases the speed of application releases and brings a certain level of agility to the organization’s database development routine. This practice facilitates the lives of database developers and database administrators (DBAs) and improves their performance.

Database change management is not easy. The database is not a simple collection of files or code fragments, and you can not just roll back to the previous version to correct a mistake as in application development. Database changes are definitely the riskiest part of any application update and as a database grows, DBAs start to keep a close eye on it to avoid unexpected issues. That’s why it is so important to automate database schema changes monitoring—to save a database team loads of time and energy.

In a multi-user environment, to keep the risks to a minimum, it is vitally important to regularly monitor changes made to the database schemas. Can the process be automated?

How to set up automatic database schema changes tracking

Prerequisites

To monitor a SQL Server database for schema changes we will use the dbForge Schema Compare tool that comes as part of the SQL Tools bundle, a custom configuration file, and PowerShell scripts.

The logic of the process

Step 1.
A custom PowerShell script initiates dbForge SQL Schema Compare, creates a database snapshot, and puts it into the D:\Monitor\Baseline folder.

Step 2.
After a specified time interval, another PowerShell script launches and initiates the schema comparison between the current database and the snapshot.

Step 3.
In case the schema differences are detected, a new snapshot is created with the current date and time and placed into the D:\Monitor\DiffSnapshots folder, a report is created and placed into the D:\Monitor\DiffReports folder, and a log file with the text: “The databases are different” is created in the D:\Monitor\Logs folder.

In case there are no schema differences found, exit code 100 is returned, and, accordingly, a new snapshot and a report are not generated, but only a log file with the text: “The databases are identical” is created in the D:\Monitor\Logs folder so that the DBA could be sure that the process has run.

Please, see the picture below to better understand the logic behind the process.

Implementation

Create the configuration file

In the configuration file D:\Monitor\Databases.txt, you need to specify the server name, the database name, authentication type, login, and password for generating a snapshot, as well as the target database to compare it with. For example:

JORDANS\MSSQLSERVER2019,AdventureWorks2019_P1,,login,password

Our configuration file looks like as below as we using Windows Authentication to connect to SQL Server.

JORDANS\MSSQLSERVER2019,AdventureWorks2019_P1,true,,

Create a custom PowerShell script to create a database snapshot

#region Variables

$rootFolder = "D:\Monitor"
$databasesTxtPath = "D:\Monitor\Databases.txt"
$diffToolLocation = "C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.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 = [boolean]($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) {
      $connection = New-DevartSqlDatabaseConnection -Server $server -Database $database -WindowsAuthentication $true       
   }
   else {
      $connection = New-DevartSqlDatabaseConnection -Server $server -Database $database -UserName $userName -Password $password
   }

   # Test database connection
   Write-Host "Testing the database connection..."
   $result = Test-DevartDatabaseConnection -Connection $connection;
   if (!$result) {
      Write-Error "Cannot connect to the database (Server: $server; Database: $database)"
      continue
   }
   
      
   Write-Host "Creating a snapshot for the Server: $server; Database: $database"
      
   # Create a snapshot
   Start-Process -FilePath $diffToolLocation -ArgumentList "/snapshot /connection:`"$connection`" /database:$database /file:`"$currentSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden        
}

You can run the script manually, schedule its execution, or use it in your CI.

On successful script execution, a new snapshot will be created in the D:\Monitor\Baseline folder.

PowerShell script to create a database snapshot

Create a custom PowerShell script to run the comparison between the snapshot and a target database

#region Variables

$rootFolder = "D:\Monitor"
$databasesTxtPath = "D:\Monitor\Databases.txt"
$diffToolLocation = "C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Schema Compare for SQL Server\schemacompare.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 = [boolean]($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.$database.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) {
      $connection = New-DevartSqlDatabaseConnection -Server $server -Database $database -WindowsAuthentication $true       
   }
   else {
      $connection = New-DevartSqlDatabaseConnection -Server $server -Database $database -UserName $userName -Password $password
   }

   # Test database connection
   Write-Host "Testing the database connection..."
   $result = Test-DevartDatabaseConnection -Connection $connection;
   if (!$result) {
      Write-Error "Cannot connect to the database (Server: $server; Database: $database)"
      continue
   }
       
    
   # Log information about checking the database 
   New-Item -ItemType File -Force -Path $logName

   # Initiate the comparison of the current snapshot with the database and generate a report   
   $process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source snapshot:`"$currentSnapshotFile`" /target connection:`"$connection`" /report:`"$currentReportFile`" /reportformat:html" -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      
      Start-Process -FilePath $diffToolLocation -ArgumentList "/snapshot /connection:`"$connection`" /database:$database /file:`"$diffSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden 
   }
}

You can run the script manually, schedule its execution, or use it in your CI.

SQL Server change tracking with dbForge

On successful script execution, a new snapshot is created in the D:\Monitor\DiffSnapshots folder, and a diff report is created in the D:\Monitor\DiffReports folder.

Create SQL Server snapshot and report

Workflow

The database administrator every morning monitors the D:\Monitor\Logs and D:\Monitor\DiffSnapshots folders and, accordingly, can see whether there have been changes.
If there were changes, there is a newly created snapshot file in the D:\Monitor\DiffSnapshots folder. Then the administrator can check the D:\Monitor\DiffReports folder to view and analyze the differences.
The database administrator can also compare the baseline snapshot from the D:\Monitor\Baseline folder and the snapshot that was automatically generated and placed into the D:\Monitor\DiffSnapshots folder.

In this simple, yet elegant way, you can use dbForge Schema Compare for SQL Server to automate the process of detecting and tracking database changes.

Scaling

The proposed worked example to automate database schema changes tracking can be scaled to multiple databases. Just make changes to the configuration file—add the databases you want to track schema changes for.

JORDANS\MSSQLSERVER2019,AdventureWorks2019_P1,true,,
JORDANS\MSSQLSERVER2019,AdventureWorks2019_P2,true,,
JORDANS\MSSQLSERVER2019,AdventureWorks2019_P3,true,,

Conclusion

The article provides a simple way of automating SQL Server database schema comparison tasks that can be scaled to multiple databases.

It is worth mentioning, that the schema compare functionality is also available in dbForge Studio for SQL Server—our all-in-one IDE that covers nearly every aspect of SQL Server database development, management, and administration. To automatically monitor a SQL Server database for schema changes, you can use either dbForge Schema Compare for SQL Server or dbForge Studio for SQL Server—just choose the tool that suits you best.

Devart products come with a free 30-day trial. Download dbForge Studio for SQL Server or dbForge Schema Compare for SQL Server and see for yourself that automation of schema changes tracking can be easy and painless.

Note
To give our customers more choice, we deliver dbForge Schema Compare for SQL Server as part of two different toolkits: dbForge Compare Bundle for SQL Server (a pack of two essential compare tools) and dbForge SQL Tools (a pack of 15 advanced tools for DB development, management, and administration )—just choose the tools pack that meets your project requirements.

Comments are closed.