Set up automatic email delivery of data comparison logs

dbForge Studio for SQL Server enables you to automatically send email notifications with the data comparison log file from the command line.

Workflow

The process of setting up automatic email delivery includes the following steps:

1. Creating a .txt file with the source and target databases and servers.

2. Creating a .ps1 file with the email settings.

3. Creating a .bat file to run comparison and email delivery.

Create a .txt file with the source and target databases and servers

1. Open any text editor.

2. Enter the connection details for the source server and database. Separate each value with a comma.

server_name1, database_name1

where:

• server_name1 – Specifies the name of the source server where the source database resides.
• database_name1 – Specifies the name of the source database you want to compare.

3. Save the .txt file, for example, as Source_Servers_and_DBs.txt.

4. Repeat the same steps for the target server and database, and save the file, for example, as Target_Servers_and_DBs.txt.

Create a .ps1 file with the email settings

To configure the sender and the recipient of the comparison results, create a Windows PowerShell cmdlet file ( .ps1).

1. Open any text editor.

2. Create a new file and enter the following script:

$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 – Specifies the sender email address.
• $emailTo – Specifies the recipient 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.

3. Save the file with a .ps1 extension, for example, as send_email.ps1.

Create a .bat file to run comparison and email delivery

1. Open any text editor.

2. Create a new file and enter the following script:

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=<user_name>" /target connection:"Data Source=%%g;Initial Catalog=%%h;Integrated Security=False;User ID=<user_name>" /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 – Specifies the default installation path for dbForge Studio for SQL Server. If you installed dbForge Studio in a different location, update the path to the dbforgesql.com file.
• Source_Servers_and_DBs.txt – Specifies the text file that contains the source servers and databases.
• Target_Servers_and_DBs.txt – Specifies the text file that contains the target servers and databases.
• D:\temp\sync_to_mail\PowerShell\send_email_script.ps1 – Specifies the path to the file that contains the script with the email settings.

3. Save the file with a .bat extension, for example, as automatic_email_delivery.bat.

Run the .bat file

Run the created .bat file to set up automatic email delivery of the log file.

Note

You can automate the execution of the .bat file by scheduling it with a task scheduler (for example, Windows Task Scheduler) to run at specific times or intervals.