Set up automatic email delivery of data comparison logs

Last modified: March 28, 2025

Data Compare for SQL Server allows you to set up automatic email delivery of the log file in both cases: when SQL data comparison goes well and when it fails. The task can be completed by means of 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:

• $emailFrom specifies 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% /datacompare /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
  )
)

pause

In the syntax above:

• Set Compare specifies the location of the Data Compare application on your PC. By default it is “C:\Program Files\Devart\dbForge Compare Bundle for SQL Server\dbForge Data Compare for SQL Server\datacompare.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% /datacompare 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

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 data comparison logs