Configure automatic email delivery of schema comparison logs

Last modified: March 28, 2025

Schema Compare for SQL Server allows you to set up automatic email delivery of the log file in both cases: when SQL schema comparison goes well and when it fails. The task can be completed using the command-line functionality.

There are three crucial steps to setting up automatic email notifications:

1. Specify Source and Target in the corresponding .txt files
2. Lay out the mail settings in a .ps1 file
3. Create a .bat file to run both, comparison and mail delivery

Set Source and Target

1. Open Notepad and specify source server(s) and database(s). Use commas as a separator:

DBFSQLSRV\SQL2019, TestDatabaseDev

2. Save the .txt file.

3. Similarly, specify target server(s) and database(s):

DBFSQLSRV\SQL2019, TestDatabaseDemo

4. Save another .txt file.

Configure the email settings

Configure the sender and the recipient of the comparison results. For this, create a Windows PowerShell cmdlet file ( .ps1). It is a script, that contains a series of lines written in the PowerShell scripting language.

1. In Notepad, type in the following code:

$emailFrom = "email_from@test.com"
$emailTo = "email_to@test.com"
$subj = "email_subject"
$body = ""
$file = "path_to_file"
$smtpServer = ""

$att = new-object Net.Mail.Attachment($file)
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = new-object Net.Mail.MailMessage

$msg.From = $emailFrom
$msg.To.Add($emailTo)
$msg.Subject = $subj
$msg.Body = $body
$msg.Attachments.Add($att)

$smtp.Send($msg)
$att.Dispose()

In the syntax above:

• $emailFromspecifies the sender’s email address
• $emailTo specifies the recipient’s email address
• $subj specifies the email subject
• $body specifies any text for the email body, if required
• $file specifies the path to the log file
• $smtpServer specifies the SMTP server of your mail service

2. Save the file as .ps1.

Create an executable file

The last step is to create an executable .bat file. This way, the task can be performed using the command line.

1. In Notepad, type in the code:

Set Compare="path_to_app"
Set Sender= powershell.exe

FOR /F "eol=; tokens=1,2* delims=, " %%e in (Source_Servers_and_DBs.txt) do (
  FOR /F "eol=; tokens=1,2* delims=, " %%g in (Target_Servers_and_DBs.txt) do (
    %compare% /schemacompare /source connection:"Data Source=%%e;Initial Catalog=%%f;Integrated Security=False;User ID=yourusername" /target connection:"Data Source=%%g;Initial Catalog=%%h;Integrated Security=False;User ID=yourusername" /log:Compare_result.log
  )

  if %ERRORLEVEL%==0 (
    %Sender% -File D:\temp\sync_to_mail\PowerShell\send_email_script.ps1
    cd.>Compare_result.log
  )
)

In the syntax above:

• Set Compare specifies the location of the Schema Compare application on your PC. By default it is “C:\Program Files\Devart\dbForge Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com”
• Source_Servers_and_DBs.txt is the name of the previously created file containing source connection(s)
• Target_Servers_and_DBs.txt is the name of the previously created file containing target connection(s)
• %compare% /schemacompare is the command that initiates the comparison
• D:\temp\sync_to_mail\PowerShell\send_email_script.ps1 is the location and name of the script with the email settings
• if %ERRORLEVEL%==0 checks if the exit code (ERRORLEVEL) of the previous schemacompare command is equal to 0 (i.e., successful execution).

2. Save the file as .bat.

3. Run the created file.

Note

You can also automate the execution of the created .bat file with the help of Windows Task Scheduler.

For more information, see How to set up email delivery for schema comparison logs