Configure the schema comparison logs email delivery

You can use the command line to automatically send schema comparison logs by email.

The process involves:

• Step 1: Create .txt files for source and target databases.
• Step 2: Create a PowerShell (.ps1) script for email settings.
• Step 3: Create a .bat file to run the comparison and email delivery.

Step 1: Create .txt files for source and target databases

1. Open a text editor.

2. Enter source servers and databases separated by commas.

source_server_name, source_database_name

3. Save the .txt file.

4. Repeat the same process for the target servers and databases.

target_server_name, target_database_name

Step 2: Create a PowerShell (.ps1) script for email settings

1. Open a text editor.

2. Create a PowerShell script (send_email.ps1) with the following code. Replace placeholder values with your actual data.

$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()

where:

• $emailFrom: The sender email address.
• $emailTo: The recipient email address.
• $subj: The email subject.
• $body: The text for the email body, if required.
• $file: The path to the log file.
• $smtpServer: The SMTP server of your mail service.

3. Save the file with the .ps1 extension.

Step 3: Create a .bat file to run the comparison and email delivery

1. Open a text editor.

2. Create a batch file (run_compare.bat) with the following script. Update paths and credentials as needed.

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
  )
)

where:

• Set Compare: The default installation path for dbForge Studio for SQL Server - C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com. If you changed it, provide the correct installation path.
• Source_Servers_and_DBs.txt: The name of the file containing source connections.
• Target_Servers_and_DBs.txt: The name of the file containing target connections.
• %compare% /schemacompare: The command that initiates the comparison.
• D:\temp\sync_to_mail\PowerShell\send_email_script.ps1: The path to the file with the email settings.
• if %ERRORLEVEL%==0: It checks whether the exit code of the schemacompare command is 0 (indicating success) and, if so, creates a log file.

3. Save the file with the .bat extension.

4. Run the created .bat file.