Automate schema comparisons with PowerShell

Last modified: March 28, 2025

The topic provides a detailed, step-by-step guide to setting up an automated process for schema comparison between a local scripts folder and a remote database. The provided workflow offers an automated solution for database specialists to efficiently and reliably deliver database changes. Specifically, it caters to scenarios where a database specialist is working on a local copy of the database and needs to regularly synchronize schema changes with a remote database. By implementing this workflow, the need for manual involvement in delivering schema changes is minimized.

How to set up automatic schema comparison with subsequent emailing of the results

The suggested automation solution involves a PowerShell script that initiates the comparison of the local scripts folder and a remote database. If any differences are detected between the source and target databases, a synchronization script is automatically sent via email, while a comparison report and log file are saved to the specified folders. The database specialist can execute the received script to update the remote database with the latest schema changes, ensuring everyone operates with the most up-to-date version. If a comparison error occurs, an email notification is sent to the designated email address, allowing for efficient problem resolution. In cases where no differences exist between the scripts folder and database, the database specialist receives an email confirming the databases are identical, and the relevant log file and report are stored in designated folders.

Prerequisites

• Installed and configured SMTP server
• Database scripts folder linked to a remote repository

Workflow

Step 1. Create a text file with the database connection details

Step 2. Create a PowerShell script file to run the job

Step 3. Optional: Schedule the PowerShell script execution

Step 4. Run the PowerShell file

Create a text file with the database connection details

Open any text editor application and enter the server connection, database name, authentication type, username, and password in a single line separated by commas.

<server>,<database>,<is_windows_auth>,<user>[,<password>]

In the provided example:

• server is the server name.
• database is the name of the database.
• is_windows_auth indicates the authentication type. When set to True, it signifies the usage of Windows Authentication, whereas False indicates the utilization of SQL Server Authentication.
• user is the username.
• password is the password. If is_windows_auth is False the password is ommited.

For example,

demo-mssql\SQLEXPRESS,AdventureWorks2022Prod1,True,sa

Once done, save the file.

Create a PowerShell script file to run the job

At this stage, you need to create a PowerShell script that performs a comparison between the scripts folder and the database. The script should send an informative email indicating whether the scripts folder and the database are identical, different, or if there is an error based on the comparison result. If differences are found, the script should attach a synchronization script to the email.

To create a PowerShell script file:

1. Open a text editor or PowerShell Integrated Scripting Environment (ISE).

2. Enter PowerShell commands in the editor.

Click to open the PowerShell script example

#region Variables
$rootFolder = "D:\Monitor"
$databasesTxtPath = "D:\Monitor\Databases.txt"
#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"
#endregion

#region SC Variables
  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")
   $DiffScriptLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffSсript") 
   $ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffReports") 
   $logsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Logs")
   $BackupDB = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Backup")    
   $srvCleanName = ($server -replace "\\", "")
   $CurrentSF = "`"D:\Monitor\ScriptFolder`" serverversion:`"15.00.0000`" collation:`"SQL_Latin1_General_CP1_CI_AS`""
   $currentSnapshotFile = Join-Path $BaselineLocation "$srvCleanName.AdventureWorks2022Dev.snap"
   $currentReportFile = Join-Path $ReportsLocation "$srvCleanName.$database.$today"
   $logName = Join-Path $logsLocation "$srvCleanName.$database.$today.txt"
   $diffScript = Join-Path $DiffScriptLocation "$srvCleanName.$database.$today.sql"
#endregion  
  
#region Mail Variables 
   # Define SMTP Server and port
   $SmtpServer = "smtp.office365.com"
   $SmtpPort = "587"

   # Define email data
   $EmailFrom = "YOURMAIL@devart.com"
   $EmailTo = "jordansandersdd@gmail.com"
   $EmailSubject = "Result of Comparison of your Script Folder with " + $database
   #$EmailBody = "This is a test email."
  
   # Define credentials (Make sure to replace 'myusername' and 'mypassword' with your actual Office365 username and password)
   $MailUsername = "YOURMAIL@devart.com"
   $MailPassword = ConvertTo-SecureString "YOURPASS" -AsPlainText -Force
   $Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $MailUsername, $MailPassword

   # Send the email
   #Send-MailMessage -From $EmailFrom -To $EmailTo -Subject $EmailSubject -Body $EmailBody -SmtpServer $SmtpServer -port $SmtpPort -UseSsl -Credential $Credentials
#endregion                

   Write-Host "Server: $server; Database: $database; isWindowsAuthentication: $isWindowsAuthentication"
   
# Create a 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 the 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 scriptsfolder:$CurrentSF /target connection:`"$TargetConnectionString`" /report:`"$currentReportFile`" /reportformat:html /includeobjects:All /log:`"$logName`" /sync:`"$diffScript`" /backup:`"$BackupDB`"" -PassThru -Wait -windowstyle hidden # /backup:`"$BackupDB`"
    
 #Write-Output "ExitCode:" $process.ExitCode      
             
# Return exit code 100 in case the databases are identical
   if ($process.ExitCode -eq 100 -and !(Test-Path $diffScript) ) {
      Add-Content -Path $logName -Value "Your scripts folder and database are identical"
      # Send the email
      $EmailBody = "Scripts Folder and database are identical. Database for Comparison: " + $database 
      Send-MailMessage -From $EmailFrom -To $EmailTo -Subject $EmailSubject -Body $EmailBody -SmtpServer $SmtpServer -port $SmtpPort -UseSsl -Credential $Credentials
      #remove the newly created report, since no differences are detected
      #Remove-Item -Path $currentReportFile".html" -Force:$true -Confirm:$false
      continue
                                                                 }

# Return exit code 101 in case the databases are not identical
   if ($process.ExitCode -eq 0 -and (Test-Path $diffScript)) {
      Add-Content -Path $logName -Value "Your scripts folder and database are not identical"
      # Send the email
      $EmailBody = "Scripts Folder and database are not identical. Database for Comparison: " + $database 
      Send-MailMessage -From $EmailFrom -To $EmailTo -Subject $EmailSubject -Body $EmailBody -SmtpServer $SmtpServer -port $SmtpPort -UseSsl -Credential $Credentials -Attachments $diffScript
      #remove the newly created report, since no differences are detected
      #Remove-Item -Path $currentReportFile".html" -Force:$true -Confirm:$false
      continue
                                                             }

   else {
      $EmailSubject = "Something went wrong during Comparison of your script folder with " + $database
      # Send the email
      $EmailBody = "During Comparison of your Script Folder with " + $database + " we got an exit code: " + $process.ExitCode
      Send-MailMessage -From $EmailFrom -To $EmailTo -Subject $EmailSubject -Body $EmailBody -SmtpServer $SmtpServer -port $SmtpPort -UseSsl -Credential $Credentials
      continue
        }
}

Note

Customize the variables in the provided script to match your project’s specific requirements.

3. Save the file with a .ps1 extension (e.g., myscript.ps1).

The provided PowerShell script is developed to use Schema Compare exit codes for its logic.

• If the tool detects that the databases are identical and returns exit code 100, the script sends an email with a message stating: “Your script folder and database are identical” to the specified email address. Additionally, a comparison report and a log file are generated and saved to the designated folders.

• In the event that the databases are not identical and the tool returns exit code 101, the script generates a synchronization script. It attaches the sync script to an email with the message: “Your script folder and database are not identical.” A comparison report and a log file are also generated in this case.

• If any other exit code is returned, indicating an error during the comparison of the script folder with the database, the script sends an email with the message: “Something went wrong during the comparison of your script folder with the database.” The script also creates and saves a log file to the designated location.

Note

You can customize the script to associate its actions with other exit codes. Refer to the Exit codes used in the command line documentation topic to see the list of all Schema Compare’s exit codes.

Optional: Schedule the PowerShell script execution

The execution of the PowerShell script can be scheduled using task scheduler programs, such as Windows Task Scheduler, which is available on most Windows operating systems.

To schedule the PowerShell script using Windows Task Scheduler, you can follow these general steps:

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 .ps1 file you have created 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 of Windows Task Scheduler.

Run the PowerShell file

To conduct a comprehensive evaluation of the scenario, we will simulate three distinct situations: the first where the scripts folder and the database are identical, the second where differences exist between the two, and the third where a comparison error arises during the process.

Case 1: No differences between the scripts folder and the database

In this case, after running the PowerShell Script, we receive an email confirming that the databases are indeed identical.

Additionally, we check the report folder and find that the comparison report has been successfully generated and saved to the specified location. The log file has also been generated.

The diffs report is generated in the HTML format. If you require a different format, you can specify it within the PowerShell script. Excel and XML are also available as options for generating the comparison report.

Case 2: The scripts folder and the database are different

In this scenario, we simulate differences between the local scripts folder and a remote database. After executing the script, we receive an email containing the synchronization script as an attachment.

Additionally, the comparison report and log file are generated and saved to the specified locations.

Case 3: An error occurs during the synchronization process

In this scenario, we deliberately introduce an error during the comparison process between the scripts folder and the database. Upon running the script, we receive an email notification stating that an issue occurred with the comparison.

The log file is saved to the Logs folder.

Explore more

Configure the schema comparison logs email delivery

Discover how to configure automatic email delivery of the log file for both successful and failed SQL schema comparisons.

Compare schemas in multiple databases from the command line

Find out how to automatically automate and schedule the daily comparison and synchronization of multiple databases using the command line.

How to set up automatic log sending in the event of a comparison error

Learn how to configure the automatic email delivery of the schema comparison log file specifically for cases where the comparison encounters failures.