Automatic Schema Comparison Log Delivery via Email

In this article, we will show how to set up automatic email delivery of the log file when a comparison of multiple SQL schemas fails. The task will be completed by means of dbForge Studio for SQL Server. Specifically, we will use its command-line functionality, allowing to perform routine tasks in both, command prompt and PowerShell. In this article, we will focus on the command prompt.

Script Creation

To complete the task, we will need the following set of files:

• TXT files with Source and Target.
• A PS file with mail settings.
• A BAT file to run both, comparison and mail delivery.

Setting Source and Target

First, we need to create two TXT files containing Source and Target connections. For this, we need to do the following:

1. Open a new Notepad document and specify source servers and databases separated by a comma. In our case we specify the following two databases:

   DBMSSQLx64\MSSQL2016, dev_database
   DBMSSQLx64\MSSQL2014, test_database

2. Save the new TXT file.
3. Do the same for Target. In our case, we specify the following:

   DBMSSQLx64\MSSQL2016, test_database
   DBMSSQLx64\MSSQL2014, dev_database

Note that to illustrate the failed comparison scenario in this article, we have intentionally included the databases that do not exist (test_database and dev_database from DBMSSQLx64\MSSQL2014).

Configuring email settings

Next, we need to configure email settings for both, sender and addressee. For this, we need to create a Windows PowerShell Cmdlet file ( .ps1). It’s a script, that contains a series of lines written in the PowerShell scripting language.

To create a PS1 file with mail settings:

1. Open a plain text editor such as Notepad.
2. Put 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()
   

3. Replace the quoted values of the following parameters with your data:
   • $emailFrom = “[email protected]” – specify the sender email address.
   • $emailTo = “[email protected]” – specify the addressee 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.
4. Save the file with the PS1 extension.

Bat File

Finally, we can put everything together, and create an executable BAT file to run the chief task via the command-line interface.

To create a BAT file:

1. Open a plain text editor such as Notepad.
2. Put the following code into the file:

   Set Compare="C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"
   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=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

   Where

   • Source_Servers_and_DBs.txt is the name of the file containing source connections.
   • Target_Servers_and_DBs.txt is the name of the file containing target connections.
   • D:\temp\sync_to_mail\PowerShell\send_email_script.ps1 is the location and name of the script with mail settings.

   For more information regarding all the aspects of the command-line syntax, refer to the product documentation.
   

3. Save the file with the .bat extension.

Script Execution

Now we have anything we need. Let’s run the created bat file and see what happens.

Comparison #1

The first comparison of the databases located on the SQL Server 2016 has been successful, the differences have been found and the log file has been emailed:

As for the rest three comparisons, all of them feature non-existing databases in Source or in Target. Thus, they all resulted in the generation of the error. In all these cases, the corresponding log file has been generated and sent to the specified email address.

Comparison #2

Comparison #3

Comparison #4

Now let’s check the inbox. As expected, we received 4 emails with the log attached:

Comparison log #1

Comparison log #2

Comparison log #3

Comparison log #4

Conclusion

In this article, we provided a solution for automatic delivery of schema comparison log files to an email when comparing schemas of multiple SQL Server databases. To complete this task, we created a bat file that copes with the task in a single click. Further, we can create synchronization task in Windows Scheduler, and the process will become a 100-percent automatic.

The command-line functionality of dbForge Studio for SQL Server provides a bunch of options and possibilities for customizing schema comparison to your specific needs. Try dbForge Studio for SQL Server and check how it can help you in your DB tasks.