有关MSSQL-Scripter（SQL Server跨平台脚本工具）的全部信息

最新推荐文章于 2024-08-29 07:44:56 发布

culuo4781

最新推荐文章于 2024-08-29 07:44:56 发布

阅读量542

点赞数

文章标签：数据库 python linux java 大数据

原文链接：https://www.sqlshack.com/all-about-mssql-scripter-the-sql-server-cross-platform-scripting-tool/

版权

One of the major challenges we face today, in the software development lifecycle, is with respect to development and deployment. As applications are deployed by moving various pieces of the SQL code between several versions, configuration, different editions, and sometimes even different platforms, deployment becomes daunting. In my opinion, scripting helps a lot and can really “grease the wheels” with addressing many scenarios that involve such complexity.

今天，在软件开发生命周期中，我们面临的主要挑战之一就是开发和部署。通过在不同版本，配置，不同版本甚至有时在不同平台之间移动各种SQL代码来部署应用程序时，部署变得艰巨。在我看来，脚本可以帮助很多人，并且确实可以“解决问题”，从而解决许多复杂的情况。

The importance of database level scripting is high, but it is getting easier by the day. Scripting is a process of generating a text file that contains the data structures and data from the database objects.

数据库级脚本编写的重要性很高，但如今变得越来越容易。脚本编制是一个生成文本文件的过程，该文本文件包含来自数据库对象的数据结构和数据。

So, do we have a SQL native tool which serves the various purposes and usage? In some cases, the answer’s a yes, and in others, it’s a no.

那么，我们是否有一个SQL本机工具可以满足各种目的和用途？在某些情况下，答案是肯定的，而在其他情况下，答案是肯定的。

Database object scripting can be done in many several ways such as:

数据库对象脚本编写可以通过多种方式完成，例如：

Generate script wizard
生成脚本向导
T-SQL
T-SQL
PowerShell
电源外壳
Mssql-scripter
MSSQL脚本
Third party tools
第三方工具

This article is all about using mssql-scripter for the task, and its simple capabilities to script the objects on a cross-platform SQL Servers.

本文全部涉及使用mssql-scripter来完成该任务，以及它在跨平台SQL Server上编写对象脚本的简单功能。

In this article, we talk about:

在本文中，我们讨论：

Mssql-scripter on Windows
Windows上的Mssql脚本
Mssql-scripter on Linux
Linux上的Mssql脚本

背景 (Background)

Microsoft recent announcement on SQL tool; a command line utility, DBFS and mssql-scripter, serves two different data management functions. In a previous article, Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe, we saw how to use DBFS tools. This article is an effort to state how Python has become a more user-friendly DBA toolset, with emphasis on the next-generation database administrators. It’s all about thinking beyond the boundaries of traditional DBAs; one needs to know and understand the contemporary toolset to work on cross-platform systems.

Microsoft最近发布的有关SQL工具的公告；命令行实用程序DBFS和mssql-scripter提供两种不同的数据管理功能。在上一篇文章，使用SQL Server工具SqlPackage.exe进行连续数据库交付（CD）中，我们了解了如何使用DBFS工具。本文旨在说明Python如何成为更加用户友好的DBA工具集，重点是下一代数据库管理员。这一切都是关于超越传统DBA的思考。人们需要了解和理解在跨平台系统上工作的现代工具集。

介绍 (Introduction)

mssql-scripter is an open-source cross-platform command line tool. This command-line tool is used for scripting SQL Server database objects and data. It’s almost similar to the Generate Scripts Wizard in SSMS with a wide range of options.

mssql-scripter是一个开源的跨平台命令行工具。此命令行工具用于为SQL Server数据库对象和数据编写脚本。它几乎与SSMS中的“ 生成脚本向导”相似，具有多种选择。

mssql-scripter is a flexible tool which lets you generate .sql files that work anywhere including on-premises SQL Server, or on Azure. These SQL files can be T-SQL scripts; data definition language or data manipulation language. These files can also be piped to standard UNIX/Linux tools such as grep or awk or sed, to help with further manipulation.

mssql-scripter是一种灵活的工具，可让您生成可在任何位置（包括本地SQL Server或Azure）上工作的.sql文件。这些SQL文件可以是T-SQL脚本。数据定义语言或数据操作语言。这些文件也可以通过管道传递到标准UNIX / Linux工具，例如grep或awk或sed，以帮助进行进一步的操作。

These scripts can even be source-controlled. These scripts can be used with DevOps deployments as well, apart from SQL Database or Warehouse. That also means that the scripts are compatible with several other cross-platform CLI tools such as sqlcmd.

这些脚本甚至可以是源代码控制的。除了SQL数据库或仓库外，这些脚本也可以与DevOps部署一起使用。这也意味着脚本与其他几个跨平台的CLI工具（例如sqlcmd）兼容。

What’s notable here is that Python is necessary for the setup to work. Most SQL DBAs may have the question ‘Why this can’t be done using PowerShell?’ I had the same mindset as well. The reason I think, is that Python is growing in popularity under the umbrella of DevOps. Another reason is the strong language processing available in Unix/Linux commands; several powerful toolset provides various ways to get the required result.

这里值得注意的是，Python是安装工作所必需的。大多数SQL DBA可能会问“为什么不能使用PowerShell做到这一点？”。我也有相同的心态。我认为，原因是Python在DevOps的保护下越来越受欢迎。另一个原因是Unix / Linux命令中强大的语言处理能力。几种功能强大的工具集提供了多种获取所需结果的方法。

先决条件 (Pre-requisites)

Install Python – The recent versions of Python include pip by default. To Install Python on Windows

安装Python –默认情况下，最新版本的Python包含pip。在Windows上安装Python

Download the Python library from the download page.

从下载页面下载Python库。
Right click the Python executable, and choose the Run as administrator option.

右键单击Python可执行文件，然后选择“以管理员身份运行”选项。
Before selecting the Install now option, enable the Add Python 3.6 to PATH. This will update the environment variable. If you miss step, you may need it manually set the environment variable.

在选择立即安装选项之前，启用将Python 3.6添加到PATH 。这将更新环境变量。如果您错过了步骤，则可能需要手动设置环境变量。
Watch the status of the setup in the installation screen. The installation should go through without a glitch in most cases.

在安装屏幕中观看设置的状态。在大多数情况下，安装应该顺利进行。

That’s all!

就这样！

点子 (pip)

This section discusses the installation and configuration of mssql-scripter. Before jumping into that, I would like to highlight some of the basics of the Python Package Management Programs.

本节讨论mssql-scripter的安装和配置。在开始之前，我想重点介绍Python软件包管理程序的一些基础知识。

What is pip?

什么是点子？

pip is a package management system used to install and manage software packages, such as those found in the Python Package Index. A package installs the packages default under site-packages.

pip是一个软件包管理系统，用于安装和管理软件包，例如Python软件包索引中的软件包。软件包将在站点软件包下默认安装软件包。

pip command has several options to install and validate the package installation process. Let’s see various pip command options:

pip命令具有多个选项来安装和验证软件包安装过程。让我们看一下各种pip命令选项：

C:\Users\ccov648>pip --help

Install the mssql-scripter package

安装mssql-scripter程序包

C:\Users\ccov648>pip install mssql-scripter

List information about the installed packages

列出有关已安装软件包的信息

C:\Users\ccov648>pip list --format columns

Show information about a package:

显示有关包装的信息：

C:\Users\ccov648>pip show mssql-scripter

Show all information about a package:

显示有关软件包的所有信息：

演示版 (Demo)

Let us take a look at some scenarios on Windows and Linux

让我们看一下Windows和Linux上的一些场景

mssql-scripter on Windows

Windows上的mssql-scripter

After performing all the above steps, the mssql-scripter is ready to use. The -h parameter of mssql-scripter provides a wide range of options. Please take a few minutes to understand the list of the parameters.

完成上述所有步骤后，即可使用mssql-scripter。 mssql-scripter的-h参数提供了广泛的选项。请花几分钟来了解参数列表。

C:\Users\ccov648>mssql-scripter -h

-h is the help switch that details all the available options used with the mssql-scripter command.

-h是帮助开关，它详细说明了与mssql-scripter命令一起使用的所有可用选项。

How to Connect to Linux SQL instance from Windows

如何从Windows连接到Linux SQL实例

C:\Users\ccov648>mssql-scripter -S 10.2.6.51 -U sa -P thanVitha@2015 -d SQLShack --schema-and-data > C:\Linux-schema-and-data.sql

How to Exclude database object from output file

如何从输出文件中排除数据库对象

For the demo, I’ve created two files: One with name test and the other, SQLShack, to generate DDL scripts of those objects that do not contain ‘test’ as their name. For this, use the –exclude-objects parameter with mssql-scripter.

在演示中，我创建了两个文件：一个文件名为name test，另一个文件SQLShack生成那些不包含“ test”作为其名称的对象的DDL脚本。为此，请将–exclude-objects参数与mssql-scripter一起使用。

C:\Users\ccov648>mssql-scripter -S 10.2.6.51 -U sa -P thanVitha@2015  --exclude-objects test -d SQLShack > c:\Linux.sql

Mssql-scripter on Linux

Linux上的Mssql脚本

To configure mssql-scripter on a Linux distribution, CentOS/RHEL follow these steps.

要在Linux发行版上配置mssql-scripter，CentOS / RHEL遵循以下步骤。

To install Python-Pip using yum

使用yum安装Python-Pip

# su
# yum install python-pip python-wheel

Now, Pip is available for the mssql-scripter package management.

现在，Pip可用于mssql-scripter程序包管理。

Install mssql-scripter package using pip:

使用pip安装mssql-scripter软件包：

#pip install mssql-scripter

Update the Python setup tools.

更新Python设置工具。

#yum upgrade python-setuptools

Run the mssql-scripter command.

运行mssql-scripter命令。

#mssql-scripter -h

How to Connect SQL Server instance on Windows from Linux server

如何从Linux服务器在Windows上连接SQL Server实例

#mssql-scripter –S <ServerName> -U sa –P <Pwd> -d powerSQL --include-schemas >/home/thanvitha/PowerSQL.sql

How to project a subset of the table data using single input pattern

如何使用单个输入模式投影表数据的子集

In the following example, the DSSPUSER data is projected into the SQL file.

在下面的示例中，DSSPUSER数据被投影到SQL文件中。

#mssql-scripter –S <ServerName> -U sa –P <Pwd> -d powerSQL --include-objects dbo.dssp_log_trail --data-only | grep “DSSPUSER”>/home/thanvitha/PowerSQL_1.sql

How to project a subset of the table data using multiple patterns of input

如何使用多种输入模式来投影表数据的子集

In the following example, the DSSPUSER data is projected into the SQL file.

在下面的示例中，DSSPUSER数据被投影到SQL文件中。

#mssql-scripter –S <ServerName> -U sa –P <Pwd> -d powerSQL --include-objects dbo.genp_log_trail --data-only | grep "DSSPUSER\|OWUSER\|JDE">/home/thanvitha/PowerSQL_2.sql

How to prepare the login script using search patterns

如何使用搜索模式准备登录脚本

The grep command is used to filter out the objects that contain “create login” in the standard output.

grep命令用于过滤标准输出中包含“创建登录名”的对象。

#mssql-scripter –S <ServerName> -U sa –P <Pwd> --logins | grep "CREATE LOGIN" >/home/thanvitha/logins.sql

结语 (Wrapping up)

In this article, conducted a walk-through of the installation and configuration of the mssql-scripter tool on Windows and Linux platforms.

在本文中，对Windows和Linux平台上的mssql-scripter工具的安装和配置进行了演练。

For a typical development, testing, and release management environment, scripting is a vital part of the job.

对于典型的开发，测试和发布管理环境，脚本是这项工作的重要组成部分。

Not every solution works for every scenario, when it comes to programming techniques, scripting techniques, or tools. But mssql-scripter certainly seems promising in many cases since this tool is compatible with multiple platforms, and contains a wide range of parameters.

在编程技术，脚本技术或工具方面，并非每种解决方案都适用于每种情况。但是，由于此工具与多个平台兼容，并且包含多种参数，因此在许多情况下，mssql-scripter肯定看起来很有希望。

There may be some limitations to scripting in some cases, which need to be checked and tested on large databases.

在某些情况下，脚本编制可能会有一些限制，需要在大型数据库上进行检查和测试。

Tell us what you think by posting your comments below!

在下面发表评论，告诉我们您的想法！