PowerShell Invoke-SQLCmd 命令入门

毫无疑问，GUI 平台中的 SQL 服务器管理效果非常好。但是，如果您更擅长传统 T-SQL 查询，那么您会发现 PowerShell Invoke-SQLCmd cmdlet 很有趣。

Invoke-SqlCmd cmdlet 为使用 SQL Server 数据库提供了更加面向 PowerShell 的体验。在本教程中，您将开始使用 Invoke-SqlCmd cmdlet 管理 SQL 数据库。

深入研究并开始简化您的 SQL 数据库管理！

先决条件

在跳转到 PowerShell Invoke-SQLCmd 命令之前，请确保您已准备好以下内容来遵循本教程：

• 运行 PowerShell 的计算机 - 本教程使用 Windows Server 2022 和 PowerShell v7.3.X。

• 具有现有数据库的 Microsoft SQL Server - 本教程使用 SQL 2022 标准版以及名为EmployeesDB 的数据库。

• 具有连接到服务器并执行操作的权限的 SQL Server 用户。

安装SqlServer模块

使用 Invoke-SqlCmd cmdlet，您可以在 PowerShell 控制台中执行 SQL 查询和脚本。因此，您可以自动执行数据库管理任务并将 SQL Server 与其他 PowerShell 命令和模块集成。是的，无需在不同的工具和界面之间切换。

但与其他 cmdlet 一样，要利用 Invoke-SQLCmd cmdlet，您必须安装 SqlServer 模块。该模块支持最新的 SQL 功能，使您可以自动化数据库开发和服务器管理。

1. 以管理员身份打开 PowerShell 并运行以下 Install-Module 命令，从 PowerShell 库下载并安装 SQLServer 模块。

Install-Module SQLServer

当提示继续安装时，输入Y并按 Enter。

2. 安装后，运行以下 Import-Module 命令，该命令不提供输出，但启用与 SqlServer 模块相关的所有命令。

Import-Module SQLServer

3. 现在，运行下面的每个命令，该命令不会产生输出，但会设置包含服务器名称、用户名和密码的适当数据的变量。这样做可以让您简化工作流程，并避免在需要时手动输入常用数据。

在整个脚本和命令中一致使用这些变量可以节省时间并减少由拼写错误或其他错误引起的错误。

# Set the SQL server name
$serverName = "mssql-vm"
# Set the SQL server credentials
$adminSqlLogin = "vmsilvamolina"
$password = "S3cur3P4ss@1"

也许您计划让这些变量持续存在。如果是这样，请将它们设置为可以在需要时调用的环境变量。

通过 PowerShell Invoke-SQLCmd 连接到 SQL Server

安装 SqlServer 模块后，您就可以开始使用 PowerShell Invoke-SQLCmd cmdlet。此 cmdlet 允许您连接到 SQL 服务器并运行查询。

在查询 SQL Server 之前，请确保您已准备好在“安装 SqlServer 模块”部分的最后一步中设置的变量。

运行以下 Invoke-SQLCmd 命令，该命令不提供输出，但执行以下操作：

• 连接到您的 SQL 服务器 ($serverName)。
• 使用指定的凭据（$adminSqlLogin 和 $password）授权连接。
• 对 SQL 服务器运行 -Query 以PRINT Hello world 文本。

Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'"

请注意，-Username 参数只能指定 SQL 帐户，不能指定 Windows 帐户。

什么都没发生吗？不完全是。如果您没有看到错误，则一切顺利。

现在，在第一步中运行相同的命令，但这次使用 -Debug 参数来公开有关该命令执行的操作的更多信息。

Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'" -Debug

完美的！在下面的输出中，查询显示没有错误的结果。输入 Y 并按 Enter 确认运行该操作。

使用 SecureString 密码连接到 SQL Server

您已使用保存您的凭据的变量成功连接到 SQL 服务器，这无疑是有效的。但请注意，对密码进行硬编码或将其放入变量会使其面临风险。为什么不尝试将您的密码转换为 SecureString？

如果您希望加密密码以提高安全性，PowerShell 会提供秘密管理模块。

运行以下命令来执行以下操作：

• ConvertTo-SecureString - 将 $password 变量的字符串值转换为存储在 $secStringPassword 中的安全字符串。
• New-Object - 使用用户名 ($adminSqlLogin) 和密码 ($secStringPassword) 创建 PSCredential 对象。

# Convert the $password variable's value to SecureString
$secStringPassword = ConvertTo-SecureString $password -AsPlainText -Force
# Create a credential object
$credObject = New-Object System.Management.Automation.PSCredential ($adminSqlLogin, $secStringPassword)

现在，运行以下命令连接到 SQL 服务器并运行打印 Hello world 文本的相同查询。这次，使用您在上一步中创建的凭据对象 ($credObject) 来授权连接。

Invoke-SQLCmd -ServerInstance $serverName -Credential $credObject -Query "PRINT 'Hello world'" -Debug

以 Windows 用户身份连接到 SQL Server

查询数据时，您通常希望使用适当的权限和访问级别来执行命令。假设您需要以 Windows 用户身份运行命令。如果是这样，您必须以该特定 Windows 用户身份启动新的 PowerShell 会话。

以 Windows 用户身份查询数据而不依赖 -Username 参数：

运行以下命令在 -ScriptBlock 中启动 PowerShell 后台作业 (Start-Job)。

后台作业以通过其凭据进行身份验证的 Windows 用户 () 的身份运行，以从 sys.databases 表查询数据。

Start-Job -ScriptBlock { `
Invoke-SQLCmd -ServerInstance $serverName -Query "select * from sys.databases"`
} -Credential (Get-Credential -Credential <USERNAME>)

记下作业的名称（即Job20），如下所示。您将需要作业名称来在以下步骤中验证作业。

现在，运行以下命令来获取 (Get-Job) 和接收 (Receive-Job) 所有 PowerShell 后台作业。确保将 更改为您在上一步中记下的一个作业名称。

-Keep 参数允许您在第一次查看后再次检索收集的流数据，并以表格格式 (Format-Table) 打印。

Get-Job -Name <jobName> | Receive-Job -Keep | Format-Table

从数据库创建和查询数据

由于您现在可以连接到 SQL 服务器，因此是时候开始更深入地使用 Invoke-SQLCmd 命令的参数了。首先，您将连接到特定数据库并创建一个包含要查询的数据的表。

1. 运行以下命令将数据库名称设置为变量 ($databaseName)，并运行 -Query 来确认数据库访问。

# Add a new variable for the database name
$databaseName = "EmployeesDB"
# Run a query to confirm the access
Invoke-SQLCmd -ServerInstance $serverName `
		-Database $databaseName `
		-Credential $credObject `
		-Query "select Count(*) from sys.tables"

查询返回数据库上创建的表的数量，如下所示：

2. 接下来，执行以下命令来运行-Query来CREATE一个名为EmployeeData和TABLE >INSERT 两个条目（记录）。

这些命令不提供输出，而是创建一个表，其中包含名为 EmployeeID、EmployeeName 和 EmployeeHireDate 的列。

$query = @"
CREATE TABLE EmployeeData (
    EmployeeID int,
    EmployeeName varchar(255),
    EmployeeHireDate varchar(255)
);
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate) 
VALUES (1, 'Victor', '2023-01-01');
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate) 
VALUES (2, 'Manuel', '2023-01-01');
"@
Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query $query

3. 创建表后，运行以下命令从新创建的表 (EmployeeData) 中检索数据。该信息存储在 $EmployeesDB 中，这是一个可以像任何其他 PowerShell 对象一样访问和操作的 PowerShell 对象。

此命令不会产生输出，但您将在以下步骤中访问数据。

$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM Employees;"

您收到错误了吗？在某些时候会发生错误，您必须学习如何处理，以下是其中的一些错误：

• 出现以下错误是因为需要更正查询的表名。将指定表名由Employees修改为EmployeeData，查询成功。

• 也许您希望在收到错误后立即中止查询执行。如果是这样，请使用 -AbortOnError 参数。就像下面的屏幕截图一样，您将看到有关错误的信息。

• 或者，您可以使用带有 $false 值的 -OutputSqlErrors 参数来禁止弹出错误消息，如下所示。请注意，此参数仅接受布尔值。

4. 最后，运行以下 foreach 语句来访问 $EmployeesDB 对象中的数据并将其输出到 PowerShell 控制台。

# Print the queried information from EmployeesDB
foreach ($Employee in $EmployeesDB) {
	Write-Output "Employee ID: $($Employee.EmployeeID)"
	Write-Output "Employee Name: $($Employee.EmployeeName)"
	Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
	Write-Output ""
}

如下所示，使用 Write-Output 命令，检索到的数据将打印到控制台。

将数据从 SQL 文件插入数据库

成功插入和查询数据感觉很好，对吧？但是，当您必须插入多个条目时，为每个条目编写查询可能会很乏味。不过不用担心。 Invoke-SQLCmd 命令的 InputFile 参数会派上用场。

不要手动编写查询，而是使用包含一个 INSERT 查询和每个条目的多个值的 SQL 文件，如下所示：

1. 打开您喜欢的文本/代码编辑器，将以下查询填充到文件中并将其另存为 run.sql。请注意，您只需专注于编写每个条目的值即可。

下面的查询将新员工插入到您的 EmployeeData 表中。

INSERT INTO EmployeeData (
    EmployeeID,
    EmployeeName,
    EmployeeHireDate
)
VALUES
    (
        3,
        'Jhon',
        '2023-01-02'
    ),
    (
        4,
        'Mike',
        '2023-01-02'
    ),
    (
        5,
        'Peter',
        '2023-01-02'
    );

2. 接下来，执行以下命令，该命令不提供输出，但从 SQL 文件 (C:\run.sql) 运行查询。

$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-InputFile "C:\run.sql"

3. 现在，运行以下 foreach 语句以输出 EmployeesDB 表中的数据。

foreach ($Employee in $EmployeesDB) {
	Write-Output "Employee ID: $($Employee.EmployeeID)"
	Write-Output "Employee Name: $($Employee.EmployeeName)"
	Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
	Write-Output ""
}

下面，您可以通过 run.sql 文件查看EmployeesDB 表中的数据，包括三个新插入的员工数据。

如果您需要指定查询超时前的秒数，请使用 -QueryTimeout 参数，如下所示。默认情况下，当您运行不带超时参数的 Invoke-SQLcmd 命令时，查询不会超时。 -QueryTimeout 参数指示命令等待 SQL 请求完成的时间。

Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-InputFile "C:\run.sql" `
		-QueryTimeout 5

操作查询输出

总体而言，您已经了解了用于执行 SQL 查询和脚本的 PowerShell Invoke-SQLCmd 命令的通用性和强大性。但您是否知道您可以自定义 Invoke-SQLCmd 命令的输出以满足您的特定要求？是的，通过附加 -OutputAs 参数。

您可以将自定义输出与其他 PowerShell 命令和模块集成，以自动化和简化数据库管理任务。

要了解如何操作查询输出：

1. 运行以下命令从 EmployeeData 表中-查询数据，并将输出作为 DataRows 存储到 $EmployeesDB >。此参数允许您以类似表格的格式显示查询结果。

该命令不会将输出打印到控制台，但您将在下面验证查询到的数据

# Store data in a variable
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM EmployeeData;" `
		-OutputAs DataRows

2. 接下来，调用 $EmployeesDB 变量来验证存储的数据。

$EmployeesDB

或者添加一个句点，然后按住 TAB 键以循环显示您可能使用的不同功能，如下所示。

3. 运行与上一步相同的命令，但这次将 DataRows 值替换为 DataSet。通过此参数值，您可以将查询结果保存到 XML 格式的对象 ($xmlEmployeeData) 中。

与前面的示例一样，此命令不会向控制台生成输出，但您将在下一步中验证输出。

$xmlEmployeeData = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM EmployeeData;" `
		-OutputAs DataSet

4. 最后，调用 $xmlEmployeeData 并验证 XML 格式的对象。

$xmlEmployeeData

或者运行以下命令以表格格式 (.Tables) 查看数据，如步骤二所示。

$xmlEmployeeData.Tables

结论

在本教程中，您已迈出了学习 PowerShell Invoke-SQLCmd 命令的第一步。您已成功连接到 SQL 数据库、运行不同的查询并操作结果输出。

在 GUI（例如 SQL Server Management Studio）中工作肯定会让数据库管理不再那么令人沮丧。但是，如果您对 Invoke-SQLCmd 命令了如指掌，则可以使用 PowerShell 完成更多任务。

为什么不使用 Invoke-SQLCmd 命令自动执行日常任务？或者从 SQL 服务器中提取数据以使用 PowerShell 进行操作？无论您下一步选择哪一步，祝您在成为 PowerShell 或 DBA 专家的旅程中好运！