Automating Schema Comparison Notifications in SQL Server

Learn how to automatically send schema comparison logs between your development and production SQL Server databases with dbForge Schema Compare. This tutorial covers setting up email notifications for synchronization failures or differences, ensuring you're always in the loop

0:00 Introduction

0:38 Prerequisite and workflow

1:09 Step 1: Creating a PowerShell script file

2:03 Step 2: Creating a BAT file

3:20 Step 3: Launching the .bat file

Code used in this video:

Step 1: Create a PowerShell script file to send an SMTP e-mail with a schema comparison log

#Input data:
$From = "[sender’s email address]"
$To = "[recipient’s email address]"
$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
$Username = "jordansanders"
$Password = "useveiiwtflclxrw"
$subject = "Synchronization log"
$body = "Database synchronization failed"
$file = "D:\logs\CompareResult.log"

#Message in the HTML format:
$message = New-Object System.Net.Mail.MailMessage $From, $To
$message.Subject = $subject
$message.IsBodyHTML = $true
$message.Body = $body
$att = new-object Net.Mail.Attachment($file)
$message.Attachments.Add($att)

#Recipient info:
$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort)
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password)
$smtp.Send($message)
$att.Dispose()
pause

Step 2: Create a BAT file to initiate schema comparison and trigger email sending in case of an error

Set Compare="C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com"
Set Sender= powershell.exe
Set ConnectionS= JordanS\SQL2022
Set ConnectionT= JordanSNew\SQL2022
Set DB1= SakilaDev
Set DB2= SakilaTest

%compare% /schemacompare /source connection:"Data Source=%ConnectionS%;Initial Catalog=%DB1%;Integrated Security=False;User ID=user_name; Password=user_password;" /target connection:"Data Source=%ConnectionT%;Initial Catalog=%DB2%;Integrated Security=False;User ID=user_name; Password=user_password;" /log:"D:\logs\LogFile.log"

echo exitcode: %ERRORLEVEL%

set Send=F
if %ERRORLEVEL% EQU 40 set Send=T
if %ERRORLEVEL% EQU 101 set Send=T
if %Send%==T (%Sender% -File D:\logs\SendLog\sendlog.ps1)

pause

👉 Check full overview of dbForge Schema Compare for SQL Server here: https://www.devart.com/dbforge/sql/schemacompare/

👉 Check full overview of dbForge Studio for SQL Server (including Schema Compare) here: https://www.devart.com/dbforge/sql/studio/

🔥 You can download dbForge Studio for SQL Server Trial (including Schema Compare) here: https://www.devart.com/dbforge/sql/studio/download.html

🔥 You can download dbForge dbForge Compare Bundle for SQL Server Trial (including Schema Compare) here: https://www.devart.com/dbforge/sql/schemacompare/download.html

🔔 Subscribe to our channel and get dozens of tutorials about Devart tools: https://www.youtube.com/c/DevartSoftware

#SQLSchemaComparison #SQLServer #Database #dbForge #Devart