将PowerShell连接到SQL Server

介绍 (Introduction)

PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.

PowerShell（又名Posh或PS）正越来越成为一种用于操作支持和某些部署方案的工具。 如果您需要将数据提取或放置到SQL Server中，则PS可以一次性完成，也可以自动完成。

There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.

有多种方法可以通过PS连接到SQL Server。 在本文中，我想介绍一些可用的选项。 如果您使用PS已有数月或数年之久，那么您就会知道执行任务的方式有多种。 因此，不用说您是否要构建用于生产环境的脚本来进行测试，测试和测试……然后再对其进行一次测试。

选项清单 (List of Options)

As an overview the following is the list of options I will go over in this article:

作为概述，以下是我将在本文中介绍的选项的列表：

• SQL Server PowerShell (SQLPS)
  SQL Server PowerShell（SQLPS）
• SQL Server Management Objects (SMO)
  SQL Server管理对象（SMO）
• .NET (System.Data.SqlClient)
  .NET（System.Data.SqlClient）

SQL Server PowerShell (SQL Server PowerShell)

SQLPS is a utility that was first released with SQL Server 2008, you may see this referenced in various ways. It exists as a (1) utility and (2) as a PS module. The utility and module are installed with the SQL Server Management tools from SQL Server 2008 and up. There are a few ways of connecting to SQL Server using this utility, and each one has strengths and weaknesses.

SQLPS是随SQL Server 2008一起首次发布的实用程序，您可能会看到以多种方式引用了此实用程序。 它以（1）实用程序和（2）作为PS模块的形式存在。 该实用程序和模块与SQL Server 2008及更高版本中SQL Server管理工具一起安装。 使用此实用程序有几种连接到SQL Server的方法，每种方法都有其优点和缺点。

SQLPS.exe (SQLPS.exe)

This is a utility that you should be able to open by typing it in the run prompt (Start > Run). A second option, right-click a node under Object Explorer, within SQL Server Management Studio (SSMS), and select “Start PowerShell”. The SQLPS utility’s main access point is using the provider “SQLSERVER:\” to browse SQL Server like a file directory. With that, based on the node you open SQLPS from will place you within that path of the provider. Under each “folder” you are in for the provider offers properties to read or set, and some methods to use for administration.

这是一个实用程序，您应该可以通过在运行提示符（开始>运行）中键入它来打开它。 第二个选项是，右键单击SQL Server Management Studio（SSMS）中“对象资源管理器”下的节点，然后选择“启动PowerShell”。 SQLPS实用程序的主要访问点是使用提供程序“ SQLSERVER：\”来浏览SQL Server，就像文件目录一样。 这样，基于您打开SQLPS的节点，您将进入提供程序的路径。 在提供程序所在的每个“文件夹”下，提供程序都提供了要读取或设置的属性，以及用于管理的一些方法。

One thing you must remember when using this utility is the version of PS you operate under is 2.0, an obvious weakness. Example: I use on Windows 10 Operating System (OS) with PS version 5.0 and SQL Server 2012:

使用此实用程序时，您必须记住的一件事是您使用的PS版本是2.0，这是一个明显的弱点。 示例：我在带有PS版本5.0和SQL Server 2012的Windows 10操作系统（OS）上使用：