Automate schema comparison between a scripts folder and a database

The topic provides a detailed, step-by-step guide to set up an automated process for schema comparison between a scripts folder and a database. This workflow allows for minimizing the manual involvement in delivering schema changes.

To automate schema comparison, we’ll create a PowerShell script file to run and execute the job.

Prerequisites

File with filters based on which schema comparison will be run.
Database scripts folder linked to a remote repository.

Set up schema comparison using the PowerShell script

1. Open PowerShell Integrated Scripting Environment (ISE).

2. Enter the following PowerShell commands in the editor:

# Path to the installation folder of of dbForge Studio for MySQL
$diffToolLocation = "C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"

# Parameters to connect to the target database
$targetConnection = "User ID=username;Database=sakila;Server=dbfmylast;Port=3306;Password=password;"

# Path to the scripts folder that contains scripts of database objects
$ScriptsFolder = "D:\sakila"

# Path to the root folder where all comparison results, such as synchronization script, comparison report, and log file, will be stored
$rootFolder = "D:\CompareResult\"

# Database name
$FinalDatabaseName = "sakila"

# Path to the file with filters for schema comparison
$filterFile = "D:\CompareResult\FilterForSC_mysql.scflt"

# Create subfolders for comparison results
$today = (Get-Date -Format "dd-MM-yyyy_HH_MM_ss")
$ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Reports") 
$LogsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Logs")
$SyncScriptLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "SyncScript")
$reportFile = Join-Path -Path $ReportsLocation "ComparisonReport.$today.xls"
$logFile = Join-Path -Path $LogsLocation "ComparisonLog.$today.log"
$syncFile = Join-Path -Path $SyncScriptLocation "ScriptSync.$today.sql"

# Start schema comparison with enabled options
Write-Output "Comparison of $FinalDatabaseName and repository folder of $clientSCT"               
Get-Date -Format "yyyy-MM-dd HH:mm:ss"  
Write-Output "" 			   

$process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare 
/source scriptsfolder:`"$ScriptsFolder`" serverversion:`"8.3.0`" collation:`"latin1_swedish_ci`" 
/target connection:`"$targetConnection`" 
/MappingIgnoreSpaces:No 
/IncludeUseDatabase:No 
/sync:`"$syncFile`" 
/filter:`"$filterFile`" 
/report:`"$reportFile`" 
/reportformat:xls 
/includeobjects:All 
/log:`"$logFile`" " -PassThru -Wait -WindowStyle Hidden 

# If databases are identical, the script will return exitcode 112
if ($process.ExitCode -eq 112) {
Write-Output "CHECK OK - There are no schema differences found between $FinalDatabaseName and the source control repository repository."}

# If databases are different, the following message appears
else {
Write-Output "Schema compare ERROR - $FinalDatabaseName database and source control repository $clientSCT have different schema!"}

3. In the syntax above, customize the variables in the provided script to match your project’s specific requirements:

$targetConnection: Connection details to the target database.
$ScriptsFolder: Path to the folder with the scripts of database objects.
$rootFolder: Path to the root folder that will store all comparison results, such as a synchronization script, a comparison report, and a log file.
$FinalDatabaseName: Database name.
$filterFile: Path to the file containing filters to be applied to schema comparison.

4. On the toolbar, click Run Script or press F5 to execute the script.

Results

The script also automatically generates a comparison report, a log file, and a synchronization script. However, the synchronization script will not be generated if the databases are identical.

Generated file