Вы можете подключиться к базе данных MySQL/MariaDB, получить данные из таблицы, добавить или обновить данные прямо из PowerShell скрипта. В этой статье мы рассмотрим несколько простых способов выполнения SQL запросов к удаленной базе данных MySQL (MariaDB) из консоли PowerShell.
Прежде всего проверьте, что удаленный SQL сервер разрешает удаленный подключения по порту 3306 (по умолчанию):
Test-NetConnection mysqlDBserver -port 3306
Затем на сервере MySQL разрешите пользователю удаленные подключения с вашего IP адреса или с любого хоста (замените IP на %):
PowerShell модуль SimplySql для подключения к БД MariaDB и MySQL
Для подключения и выполнения запросов к MySQL/MariaDB базе можно использовать универсальный PowerShell модуль SimplySql. Этот модуль можно установить из PowerShell Online Gallery:
Install-Module -Name SimplySql
Для подключения к MariDB/MySQL серверу используется командлет Open-MySQLConnection, а для выполнения SQL запросов — Invoke-SQLQuery.
Сохранить имя пользователя и пароль для подключения к SQL серверу:
Подключиться к базе данных MySQL/MariaDB на удаленном сервере:
$sqlConnect = Open-MySqlConnection -ConnectionName MyDBCon -Server 192.168.158.129 -Database myDB -Port 3306 -Credential $creds -WarningAction SilentlyContinue
Выполнить запрос к таблице MySQL с параметром:
$cityname="MSK"
$data = Invoke-SqlQuery -query "SELECT * FROM myDB WHERE city like '%$cityname%'"
Вставить данные в таблицу:
Обновить значение поля в таблице MySQL:
Invoke-SqlQuery -query "UPDATE myDB SET city = 'MSK' WHERE ID = 233"
Закрыть подключение к базе данных:
Close-SqlConnection -connectionname MyDBCon
Выполнение SQL запросов из PowerShell через MySQL коннектор
Примечание. Не обязательно ставить полную версию MySQL .NET Connector, достаточно скопировать на компьютер файл библиотеки MySql.Data.dll.
#подключение библиотеки MySql.Data.dll
Следующий скрипт с помощью SELECT запроса получит данные из таблицы БД и покажет их в консоли PowerShell.
Devart’s dbForge product line offers some of the best tools for database comparison and synchronization: Schema Compare and Data Compare. Both tools are available as standalone solutions or features within their multifunctional Studios – IDEs tailored for major database systems like SQL Server, MySQL, Oracle, and PostgreSQL. This article delves into automating database comparisons using dbForge Studio for SQL Server and Windows PowerShell.
Automation of database comparisons with PowerShell
The process is simple:
- In a dedicated text file, specify which databases should be compared.
- Create a PowerShell script with the task configuration.
- Execute the script and analyze the results.
In this case, the PowerShell script will initiate the comparison of databases and generate a comparison report and a log file with detailed information about the comparison process.
Setting up the databases
First of all, it is necessary to define the databases to be compared. In our scenario, we have a database that we need to compare to several other databases. This single database must be defined in the PowerShell script – we are going to examine this point further in our article. The databases we need to compare to that “original” database should be specified in a separate text file. Also, we need to provide the connection details for each database in that text file.
In the provided example:
- server is the name of the server where the target database is located.
- database is the name of the database.
- is_windows_auth defines the authentication type. If it is set to True, it uses Windows Authentication, while False means utilizing SQL Server Authentication.
- user is the username.
- password is the password. If we apply the SQL Server Authentication (is_windows_auth is False), the password is not needed.
A simple option to retrieve these details is turning to “Save Command Line” in the Schema Compare feature of dbForge Studio for SQL Server. By clicking that button, you can get an automatically generated string with all the details for the target database.
This way, you can compile the list of all databases for comparison – you can specify as many databases as needed. Note that each database must be on a separate line like below:
Data Source=demo\SQLEXPRESS02;Initial Catalog=AdventureWorks2022Dev;Integrated Security=False;User ID=sa
Data Source=demo\SQLEXPRESS;Initial Catalog=AdventureWorks2022Test;Integrated Security=False;User ID=sa
Data Source=demo\SQLEXPRESS;Initial Catalog=AdventureWorks2019;Integrated Security=False;User ID=saYou can compare your database to other databases, no matter if they are stored on the same server or on different servers. You only need to specify the necessary connection details correctly.
Once done, save this file. We give it the name Databases.txt, you may name it as it suits you, but the path to that file is essential for the task configuration, as we must define it in the PowerShell script. In our example, we save the Databases.txt file in the root directory of the D drive.
The next step is preparing a PowerShell script that will command dbForge Studio for SQL Server to perform the task.
Creating the PowerShell script
The script for PowerShell that you are going to create at this stage will launch the task, generate comparison reports and log files, and save them in the designated folder.
To create the script, open any text editor or the PowerShell Integrated Scripting Environment (ISE) and enter the PowerShell commands in the editor.
Below you can see an example. Pay attention to the paths to the Databases.txt file and to dbForge Studio for SQL Server. Make sure you customize these parameters according to your specific environment and project’s requirements.
#region Variables
$rootFolder = "D:\Monitor"
$databasesTxtPath = "D:\Monitor\Databases.txt"
$diffToolLocation = "C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"
$SourceConnectionString = "Data Source=demo\SQLEXPRESS02;Initial Catalog=AdventureWorks2022;Integrated Security=False;User ID=sa"
#endregion
foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
# Read the connection parameters for the current database from the configuration file
$TargetConnectionString = "$line"
$today = (Get-Date -Format "dd-MM-yyyy_HH_MM_ss")
$ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffReports")
$logsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Logs")
$srvCleanName = ($server -replace "\\", "")
$currentReportFile = Join-Path $ReportsLocation "$srvCleanName.$database.$today"
$logName = Join-Path $logsLocation "$srvCleanName.$database.$today.txt"
# Initiate the comparison of the Source Database with the multiple Targer Databases and generate a report
$process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source connection:`"$SourceConnectionString`" /target connection:`"$TargetConnectionString`" /report:`"$currentReportFile`" /reportformat:html /includeobjects:All /log:`"$logName`"" -PassThru -Wait -windowstyle hidden
# Return exit code 100 in case the databases are identical
if ($process.ExitCode -eq 100) {
Add-Content -Path $logName -Value "The databases are identical"
#remove the newly created report, since no differences are detected
Remove-Item -Path $currentReportFile".html" -Force:$true -Confirm:$false
continue
}
else {
Write-Host "We have difference!More information in log file `"$logName`" and in report"
}
}Save your file as a PowerShell script with the .ps1 extension (e.g., Task.ps1).
Now you can execute it to launch the database comparison.
Executing the PowerShell script
The Task.ps1 file can be executed whenever you wish to compare your database with several other databases. Or, you can schedule this task and run it regularly, using the Windows Task Scheduler. Let us check how the PowerShell script works at the moment.
Right-click the Task.ps1 file and select Run with PowerShell. Or, you might prefer using the PowerShell ISE as it allows more visualization options.
The task is done, and the Studio has also generated reports and log files with detailed information about the task performance. Now we can review them.
Analyzing the generated reports
dbForge Studio for SQL Server generates reports for each comparison of two databases and saves them in the dedicated folder. In our case, we specified the HTML format for the reports and designated the DiffReports folder to store these files. After the task is performed successfully, we navigate to the necessary folder and view the reports there.
- Date of generation
- Source and Target database information
- Comparison results in the form of a list – you can explore each result for more information
- Dedicated synchronization script for deploying changes
- Warnings
Reviewing the logs
The logs for each “pair” are saved in the Logs folder – the location should also be specified in the PowerShell script. Navigate to that folder to check the log files:
Note that we have explored the automation of tasks of the database schemas comparison. dbForge Studio for SQL Server also includes the Data Compare feature that allows table data comparison and synchronization. The configuration of the task is similar – you only need to select Data Comparison.
Conclusion
The automation of database schema comparison provides more than just streamlining the often cumbersome and error-prone task. It ensures accuracy and consistency, eliminating human error and allowing businesses to reallocate resources to more important tasks. The benefits also extend to collaboration, as teams can rapidly identify and address changes and work cohesively to implement or revert them.
With dbForge Studio for SQL Server, comparing databases, including their schemas and table data, becomes effortless. Its robust automation capabilities even allow simultaneous comparison of one database with multiple databases. This can be executed seamlessly through a PowerShell script and scheduled for regular execution within the Studio.
Explore this feature, along with numerous others, by taking advantage of the fully functional free trial of dbForge Studio for SQL Server. Experience the trial under a full workload, test all of its capabilities, and witness the immediate growth of your productivity!
Manage SQL Server and Azure SQL Database with PowerShell scripts
The free tool PowerShell Scripts for SQL Server provides helpful scripts to automate and manage various tasks and operations for Microsoft SQL Server and Microsoft Azure SQL Database. It helps you to:
- Automate the management tasks for SQL Servers’ Database Engine, and its Analysis, Integration, and Reporting Services.
- Reduce the learning curve, saves time, and minimizes errors by providing ready-to-run scripts for many common tasks.
- Customize the tasks by providing scripts you can edit and connect to other scripts.
- Run tasks on remote servers and schedule tasks via executable scripts.
The collection of PowerShell scripts include scripts to:
- Initialize PowerShell modules
- Administer the Database Engine, and the Analysis, Integration, and Reporting Services
- Adhere to the Center for Internet Security (CIS) Benchmarks
- Manage in-memory databases
- Tune the Query Store
What is New in PowerShell Scripts for SQL Server 7.0:
The release of PowerShell Scripts for SQL Server version 7.0 in 2024 focuses
addressing numerous customers submitted suggestions by adding new scripts,
refactoring scripts, and replacing deprecated scripts.
- Added new scripts:
- New scripts for the Azure Az PowerShell module (-IAz scripts)
- – The Az PowerShell module is a set of cmdlets for managing Azure resources directly from PowerShell. PowerShell provides powerful features for automation that can be leveraged for managing your Azure resources, for example in the context of a CI/CD pipeline.
- New script for the Database Engine that uses the SQL Management Objects (SMO) Table.CheckTable() method to test the integrity of database pages implementing store for the referenced table and indexes referenced (Start-ISqlCheckTable)
- New script for performing SQL Server security assessments
- New script for General Utility for retrieving SQL Server inventory data
- New script for Initialization for SQL Server 2019 to support new SQL Server PowerShell module
- Refactored existing scripts to work with PowerShell 7, .NET core, and SQL Server 2019
- Replaced deprecated scripts:
- Replaced deprecated Get-WmiObject cmdlet usage with new Get-CimInstance cmdlet for improved security
- Replaced deprecated Get-EventLog cmdlet with new Get-WinEvents cmdlet
Database Engine
- Start and stop services
- Connect to an instance, and get connection information
- Manage databases, and script out databases
- Set adaptive query processing
- Manage policies and error logs
- Manage firewall rules, logins, assemblies, backups, and agent jobs
- Get extended event information
Center for Internet Security (CIS) Benchmarks
- Surface area reduction
- Authentication and authorization
- Password policies
- Auditing and logging
- Application development
- Start and stop SQL Server browser service
In-memory Databases
- Get overall memory utilization
- Get size on disk
- Get detailed utilization by table
- Get checkpoint size
Query Store
- Enable and disable Query Store
- Get information about query plans in Query Store
- Get activity status
- Set maximum data retention size
- Set automatic tuning of queries via Query Store
General Utilities
- Get list of registered instances
- Ping instances and get their status
- Set aliases for instances
- Get advanced properties by service
- Add service account to local security privilege
- Get and set power plans
Analysis Services
- Connect to an instance, and get connection information
- Get a list of databases
- Backup databases, manage backup retention, and restore databases
Integration Services
- Start and stop services
- Connect to an instance, and get connection information
- Manage packages and catalogs
- Publish projects
Reporting Services
- Start and stop services
- Connect to an instance, and get connection information
- Backup and restore encryption keys
- Get configuration information
- Get event log information
Azure SQL Database
- Initialize PowerShell module
- Set scaling level, and set auditing and threat detection
- Get database properties
- Get alert definitions, and set and get alert rules
- Get query results, and get query metric data
PowerShell is a flexible tool that can be used for both data analytics and data orchestration. Of course, that data must come from somewhere. In this two-part article, I want to demonstrate how to use PowerShell to interact with SQL Server data.
Reading SQL Server data into can be useful in any number of scenarios. For instance, if your organization maintains a SQL-based HR database, you could use PowerShell to orchestrate the employee onboarding process. Similarly, PowerShell can function as a reporting tool for business data.
As I’m sure you can imagine, each SQL Server deployment is unique, hence there is no one-size-fits-all technique for connecting PowerShell to SQL Server. To illustrate the connectivity process, I am going to begin by guiding you through the steps of setting up a SQL Server Express deployment and adding sample data to it. Once that data is in place, I will explain how to access it with PowerShell – and even use PowerShell to save new data to the database.
Create a Sample SQL Server Database
For this article, I have decided to use SQL Server 2022 Express. For those unfamiliar with SQL Server Express, it is a specialized edition designed for desktop use or small server applications. I opted for this edition purely for the sake of simplicity, as I installed it on the same desktop where I will run my PowerShell code. However, with minor adjustments, the techniques I am going to demonstrate should apply to any currently supported SQL Server Edition.
Step 1. Deploy SQL Server
After completing the installation process, you will see a summary screen, similar to the one shown in Figure 1 below. As a best practice, I recommend taking a screenshot of this screen because it contains important information. If you can’t capture all the information within a single screenshot, consider copying the information to your clipboard and pasting it into a Word document.
PowerShell SQL 1
Make note of the information provided after the installation has completed.
Once you have documented the information, click the Install SSMS button. This will prompt the Setup Wizard to open a web page where you can download the SQL Server Management Studio. The SSMS installation process is completely automated.
Step 2. Create a database
Now that SQL Server is up and running, our next step is to create a database that we can use with PowerShell. In real-world scenarios, you would typically connect to an existing database that belongs to one of your applications. However, for the sake of teaching you how to connect to and interact with SQL Server from PowerShell, I think it’s better to set up a sample database rather than practice on a production database.
To get started, launch the SQL Server Management Studio. When the tool starts, use the information provided to you at the end of your SQL Server deployment to connect to your SQL Server. You can see what this connection process looks like in Figure 2.
PowerShell SQL 2
You will need to input the required information to connect to your SQL Server.
Once connected to the SQL Server, the SQL Server Management Studio will display a series of tabs (called the Object Explorer) on the left side of the screen. Right-click on the Databases tab and select the New Database command from the shortcut menu. You will need to provide a name for the new database you are creating. For this example, I am naming the database MyAppDB, as shown in Figure 3. Click OK to accept all default options and create the database.
PowerShell SQL 3
Provide a name for the new database and click OK.
Step 3. Create a database table
With the database now created, expand the MyAppDB object (or whichever name you chose for your database) in the Object Explorer. Right-click on Tables and then select the New > Table commands from the shortcut menus, as shown in Figure 4.
Right-click on Tables and choose the New > Table commands.
At this stage, you will be prompted to specify the columns you wish to include in the table. For each column, you will need to provide a column name and a data type. Since the goal here is to demonstrate the fundamentals of accessing SQL Server from PowerShell, I don’t want to create anything overly complex. As such, I am going to create a text column named Month and an integer (Int) column named ItemsSold. You can see how this looks in Figure 5.
PowerShell SQL 5
I have added two columns to my table.
After specifying the columns, close the table, and when prompted, provide a table name. In my case, I will simply name the table Table1.
Step 4. Add data to the table
The final step before we can move on to use PowerShell is to populate the table that we just created with some data.
First, refresh the Object Explorer. Right-click on the table you created (e.g., Table1) and select the Edit Top 200 Rows command from the shortcut menu. Now you can begin entering data into the table, as shown in Figure 6.
Normally, you would use an application to populate a database table, but since no application exists for this particular database, using SSMS is the next best option.
PowerShell SQL 6
You can use SSMS to add data to the database.
With data now in the database, we can begin using PowerShell to analyze it. I will show you how in the second part of this article.
Access the SQL Server Database From PowerShell
Connecting to SQL Server from Windows PowerShell has long been considered a difficult task. The reason for this arose from having to use a .NET connection string to establish the connection. While this process does work, it is far from being intuitive. Thankfully, Microsoft has greatly simplified the process with the introduction of the Invoke-SQLCmd cmdlet.
Step 1. Allow remote connections
Open the SQL Server Management Studio.
Right-click on the server name (within Object Explorer).
Select the Properties command from the shortcut menu. This will open the Server Properties window.
Select the Connections tab.
Check the “Allow Remote Connections to this Server” checkbox.
Step 2. Install the PowerShell module
To access the sample database using PowerShell, you must install the SQL Server module. If you are running SQL Server locally on the system, the module might already be installed.
PowerShell SQL 2-1_0
This is how you install the SQL Server module for PowerShell.
If you receive an error message while importing the module, you may need to temporarily set the system’s execution policy to be less restrictive. You can see an example of this in Figure 8.
PowerShell SQL 2-2
I had to set my execution policy to unrestricted before I could import the SQLPS module.
Step 3. Perform a SQL Query
Now that the SQL Server module is installed, you can access SQL Server from PowerShell. The old .NET-based technique required you to set up a connection string, establish connectivity to the SQL Server, and then enter a SQL Server query. However, the Invoke-SQLCmd cmdlet simplifies things. When using this command, you connect to the SQL Server and issue a query with a single command.
To demonstrate how this works, let’s read the contents of the table we created in the first part of this article. Here is the basic command structure:
$Data = Invoke-SQLCmd -ServerInstance -Database -Query “To make this command work, you only need to plug in the details from your SQL Server instance. However, before you do, there are two potential “gotchas” you must know about:
Instance Name Format: The instance name might not be as it seems. If you think back to Part 1, SQL Server Setup reported the instance name as SQLEXPRESS. However, if you use SQLEXPRESS as the instance name, PowerShell will return an error saying that a network-related or instance-specific error occurred and that the server was not found or is not accessible. To resolve this, enter the instance name in the format . For example, in my case, the instance name would have to be entered as Win11SQLSQLEXPRESS, as shown in Figure 9. In that figure, you can also see that the data from the table was written to a PowerShell variable named $Data.
PowerShell SQL 2-3
I have retrieved data from anSQL Server table.
Write data to SQL Server
You can also use PowerShell to write data to your database table using the Invoke-SQLCmd cmdlet. The key difference is that instead of using a Select statement, you would use an Insert Into statement. For example, if I wanted to insert a numerical value for July into my table, which is named Table1, the command would look like this:
$Data=Invoke-SQLCmd -ServerInstance Win11SQLSQLEXPRESS -Database MyAppDB -Query “Insert Into Table1 (Month, ItemsSold) Values (‘July’,35)”This command inserts the values July and 35 into the Month and ItemsSold columns, respectively. You can see the command in action in Figure 10.
PowerShell SQL 2-4
You can insert data into a SQL table from PowerShell.
Keep in mind you can insert not only literal values but also values stored in variables into your database table.
What we have covered so far only scratches the surface of what’s possible with PowerShell and SQL. In fact, you can use PowerShell to build comprehensive database-driven applications and perform advanced data analytics. I will explore additional techniques in future articles.
