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.
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:
- 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
- Save the new TXT file.
- 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:
- Open a plain text editor such as Notepad.
- 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()
- 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.
- Save the file with the PS1 extension.
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:
- Open a plain text editor such as Notepad.
- 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:\t emp\sync_to_mail\PowerShell\send_email_script.ps1 cd.>Compare_result.log ) ) ) pause
- 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.
- Save the file with the .bat extension.
Now we have anything we need. Let’s run the created bat file and see what happens.
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.
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
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.