Data and Schema Comparison Log Delivery to Email

In this article, we will demonstrate to you how to set up automatic email delivery of the log file in both cases: when SQL data and schema comparison goes well and when it fails. The task will be completed by means of dbForge Data Compare and Schema Compare. Specifically, we will use the tools’ command-line functionality to configure the automatic log delivery.

Create the script

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

To begin with, let us specify what exactly we are going to compare:

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

DBFSQLSRV\SQL2016, BicycleStoreDev
DBFSQLSRV\SQL2019, TestDatabaseDev

2. Save the .txt file.

3. Repeat the same procedure for the Target.

DBFSQLSRV\SQL2016, BicycleStoreDemo
DBFSQLSRV\SQL2019, TestDatabaseDemo

As you can see, in our case, we are going to compare the development and production versions of the databases: TestDatabaseDev and BicycleStoreDev in the red corner, and TestDatabaseDemo and BicycleStoreDemo in the blue corner of the ring.

Note: In order to illustrate the failed comparison scenario better, we have intentionally included the non-existent databases in the comparison. Spoiler: TestDatabaseDev and TestDatabaseDemo are the imposters.

Configure the email settings

The next item on our agenda is to configure the sender and the recipient of the comparison results. For this, we need to 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 protected]"
$emailTo = "[email protected]"
$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()

2. In order to cater it according to your particular needs, replace the quoted values of the following parameters with your data:

• $emailFrom = “[email protected]” – specify the sender’s email address.
  $emailTo = “[email protected]” – specify the recipient’s email address.
  $subj = “email_subject” – specify the mail subject.
  $body = “” – specify any text for the mail body, if required.
  $file = “path_to_file” – specify the path to the log file.
  $smtpServer = “” – specify the SMTP server of your mail service.

3. Save the file as .ps1.

Create an executable file

The last step in our scenario would be assembling all the previously prepared pieces into a single .bat file. This way, the task can be performed using the command line.

1. In Notepad, type in the following 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% /comparison_command /source connection:"Data Source=%%e;Initial Catalog=%%f;Integrated Security=False;User ID=sa" /target connection:"Data Source=%%g;Initial Catalog=%%h;Integrated Security=False;User ID=sa" /log:Compare_result.log

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

)
)

pause

2. In the syntax above, mind the following:

• Set Compare=”path_to_app” – depending on what exactly you are comparing (data or schemas), specify either:
  • “C:\Program Files\Devart\dbForge Compare Bundle for SQL Server\dbForge Data Compare for SQL Server\datacompare.com”
  • or “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 connections.
  Target_Servers_and_DBs.txt is the name of the previously created file containing target connections.
• %compare% /comparison_command – once again, depending on what exactly you are comparing, replace comparison_command with either:
  • datacompare
    or
  • schemacompare.
• D:\temp\sync_to_mail\PowerShell\send_email_script.ps1 is the location and name of the script with the email settings.

For more information regarding all the aspects of the command-line syntax, refer to the dbForge Data and Schema Compare documentation.

3. Save the file as .bat.

Execute the script

Finally, as we have already gathered all the “infinity stones”, we can proceed with our objective and run the created .bat file.

Scenario 1

The first example demonstrates a successful comparison. The differences have been located, and the log file has been emailed:

Data Compare

Schema Compare

The other three scenarios feature a non-existing database either in Source or Target (or both). Therefore, all of the following comparisons result in an error. Regardless, the corresponding log files are still generated and sent to the specified email address.

Scenario 2

Data Compare

Schema Compare

Scenario 3

Data Compare

Schema Compare

Scenario 4

Data Compare

Schema Compare

Email Notifications

It is now time to open our mailbox and see if there are any notifications. As expected, there are eight letters: one for each of the above-mentioned scenarios. To see the results, open a letter and look for the attached .txt file with the detailed comparison report.

Upon opening the attached log file, you will be presented with a comprehensive report detailing how the comparison process was conducted and its outcomes:

Comparison log #1

Data Compare

Schema Compare

Comparison log #2

Data Compare

Schema Compare

Comparison log #3

Data Compare

Schema Compare

Comparison log #4

Data Compare

Schema Compare

Conclusion

In this article, we focused on how to get automatic email notifications with data and schema comparison log files after comparing multiple SQL Server databases. For this purpose, we created a .bat file that can complete the task in a single click. To go even further and make the process completely automatic, you can create a synchronization task in Windows Scheduler. dbForge Compare Bundle for SQL Server provides a bunch of command-line options and possibilities for customizing data and schema comparison to your specific needs. Try a 30-day free trial and check how it can help you in your day-to-day database tasks.