osql sql2012_您可能不知道SQL Server实用程序-SQLdiag，osql和SqlLocalDB

osql sql2012

介绍 (Introduction)

There are several tools included in SQL Server on-premises that you may not know.

您可能不知道SQL Server内部部署中包含的一些工具。

In this article we will explain the following tools:

在本文中，我们将解释以下工具：

SQLdiag
SQLdiag
osql
osql
SqlLocalDB
SqlLocalDB

After reading this article, you will learn how to use them and understand what are they for.

阅读本文之后，您将学习如何使用它们并了解它们的用途。

要求 (Requirements)

SQL Server on-premises Installed 2016 (older versions can be used and later versions may not include osql).
SQL Server本地安装2016（可以使用旧版本，更高版本可能不包含osql）。

入门 (Getting Started)

1. SQLdiag

This tool is used by Microsoft tech support to diagnostic problems with SQL Server. However, it contains useful information that you can use to detect SQL Server problems. You can collect performance counter information, locks, deadlocks, event logs, SQL Server configuration, OS information and traces.

Microsoft技术支持人员使用此工具来诊断SQL Server问题。但是，它包含有用的信息，可用于检测SQL Server问题。您可以收集性能计数器信息，锁，死锁，事件日志，SQL Server配置，操作系统信息和跟踪。

In drive:\Program Files\Microsoft SQL Server\130\Tools\Binn, you will find the SQLdiag (unless you specify another folder using the /O parameter):

在drive：\ Program Files \ Microsoft SQL Server \ 130 \ Tools \ Binn中，您将找到SQLdiag（除非您使用/ O参数指定另一个文件夹）：

osql sql2012_您可能不知道SQL Server实用程序-SQLdiag，osql和SqlLocalDB

It collects log and data from your server to troubleshoot problems.

它从您的服务器收集日志和数据以解决问题。

Using SQLdiag /? or SQLdiag -?, you can get help about parameters and the command use:

使用SQLdiag /？或SQLdiag-?，您可以获得有关参数和命令使用的帮助：

In order to start, in the cmd, write SQLdiag:

为了开始，在cmd中，编写SQLdiag：

If there are warnings and red messages, you will need to press escape to continue collecting data. It takes a while to collect all the data. You can always press Control+C to stop. In this example, try to wait 15-30 minutes after stopping the data collection:

如果有警告和红色消息，则需要按Escape键才能继续收集数据。收集所有数据需要一段时间。您可以随时按Control + C停止。在此示例中，尝试在停止数据收集后等待15-30分钟：

SQLdiag collects the information of all the instances. You can specify the machines used in the data collection using the /M parameter. You can also specify the start and end date of the data collection using the /B and /E parameters (to begin and end the data collection).

SQLdiag收集所有实例的信息。您可以使用/ M参数指定在数据收集中使用的计算机。您还可以使用/ B和/ E参数指定数据收集的开始和结束日期（以开始和结束数据收集）。

In drive:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqldiag\internal or a similar path, you will find the diagnostic files created:

在drive：\ Program Files \ Microsoft SQL Server \ 130 \ Tools \ Binn \ sqldiag \ internal或类似路径中，您将找到创建的诊断文件：

The ##console file contains a resume of all the files collected by the SQLdiag:

## console文件包含SQLdiag收集的所有文件的履历：

##Envvars file contains all the environment variables of the operative system:

## Envvars文件包含操作系统的所有环境变量：

##Files contains the dir information of the SQLdiag folder:

## Files包含SQLdiag文件夹的目录信息：

##SQLdiag contains information of the data being collected:

## SQLdiag包含正在收集的数据的信息：

The ##Server_MSDiagProcs_Startup file creates some stored procedures in the tempdb:

## Server_MSDiagProcs_Startup文件在tempdb中创建一些存储过程：

You also have in the drive:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqldiag, some log files that can be useful:

您还可以在驱动器中找到：\ Program Files \ Microsoft SQL Server \ 130 \ Tools \ Binn \ sqldiag，其中一些有用的日志文件：

The log files can be opened in SQL Server profiler, they are trace logs:

可以在SQL Server事件探查器中打开日志文件，它们是跟踪日志：

In the ServerName_sp_sqldiag_Shutdown file, you can see the errorlog information including SQL Server version, Service Pack and errors:

在ServerName_sp_sqldiag_Shutdown文件中，您可以看到错误日志信息，包括SQL Server版本，Service Pack和错误：

The Server_Name_MSINFO32. Contains system information like the system, name, processor, BIOS, IRQ. It is the information that can be obtained from the systeminfo command:

Server_Name_MSINFO32。包含系统信息，例如系统，名称，处理器，BIOS，IRQ。这是可以从systeminfo命令获得的信息：

As you can see, SQLdiag collects a lot of information about traces, logs, hardware, files, paths and more. It can be very useful to detect the cause of an error.

如您所见，SQLdiag收集了大量有关跟踪，日志，硬件，文件，路径等的信息。检测错误原因可能非常有用。

It is also possible to run SQLdiag in quiet mode using the /Q argument. You can also run the SQLdiag as a service using the /R argument to register SQLdiag as a service.

也可以使用/ Q参数在安静模式下运行SQLdiag。您也可以使用/ R参数将SQLdiag作为服务运行，以将SQLdiag注册为服务。

2. osql

Some students asked me what is osql.

一些学生问我什么是osql。

In the drive:\Program Files\Microsoft SQL Server\130\Tools\Binn\ or a similar path, you will find the osql:

在驱动器：\ Program Files \ Microsoft SQL Server \ 130 \ Tools \ Binn \或类似路径中，您会找到osql：

I could not believe that osql was still in SQL Server 2016. According to the documentation osql will be removed soon. I heard that rummor a long time ago, but it is still there.

我不敢相信osql仍在SQL Server 2016中。根据文档说明，osql将很快被删除。我很久以前就听说过这种谣言，但它仍然存在。

Osql is the old command line used in SQL Server 2000. Before that, the command line was isql (removed after SQL Server 2005). Now, SQL Server uses sqlcmd.

Osql是SQL Server 2000中使用的旧命令行。在此之前，命令行是isql（在SQL Server 2005之后删除）。现在，SQL Server使用sqlcmd。

Why should I care about this old command line tool? The answer is that it can be useful if you have to deal with old SQL Server versions.

我为什么要关心这个旧的命令行工具？答案是，如果您必须处理旧SQL Server版本，它可能会很有用。

Osql uses ODBC to connect to the server, sqlcmd uses OLEDB and it is faster.

Osql使用ODBC连接到服务器，而sqlcmd使用OLEDB，并且速度更快。

Sqlcmd supports parametrized parameters, XML, connection to Azure, DAC. Osql do not support that.

Sqlcmd支持参数化参数，XML，与Azure，DAC的连接。 Osql不支持。

To get the help you can run the osql /? or osql -?:

要获得帮助，您可以运行osql /？还是osql-？

If you are familiar with sqlcmd, you will easily be able to learn this tool, because many arguments are exacly the same like the -i argument to run scripts or the -o to store results in an outoput file. Note that you need to be careful with the uppercased or lowercased arguments. For example the -O argument uses the isql behavior and -o lowercased is to handle output files.

如果您熟悉sqlcmd，则可以轻松学习此工具，因为许多参数完全相同，例如-i参数用于运行脚本，或-o参数用于将结果存储在输出文件中。请注意，您需要小心使用大写或小写参数。例如，-O参数使用isql行为，而-o小写字母用于处理输出文件。

To connect to osql, you can use the osql -E to connect to SQL Server using the trusted connection. If the instance name is not the default instance you may need to add the server parameter (-S Instance name):

要连接到osql，可以使用osql -E通过受信任的连接连接到SQL Server。如果实例名称不是默认实例，则可能需要添加服务器参数（-S实例名称）：

The funcionallity is like sqlcmd. You need to write your query, press Enter and then press GO and Enter again:

功能类似于sqlcmd。您需要编写查询，按Enter，然后按GO，然后再次按Enter：

The following example shows how to get the server name in osql:

以下示例显示如何在osql中获取服务器名称：

You can run backups, create tables, indexes, queries or any administrative task related to T-SQL in osql. It my be useful if you need for some reason to handle an old SQL Server version that does not include UI (Enterprise Manager or Query Analyzer) or if you want to automate tasks in old SQL Server versions.

您可以在osql中运行备份，创建表，索引，查询或与T-SQL相关的任何管理任务。如果您出于某种原因需要处理不包含UI（企业管理器或查询分析器）的旧SQL Server版本，或者要自动执行旧SQL Server版本中的任务，这对我很有用。

3. SqlLocalDB

Another tool included is SqlLocalDB. This tools is used by developers to install a light SQL Server that can be installed easily in few minutes. To install a SQL Server instance, you need the SQL Installer and configure several options. With SqlLocalDB, you can have an instance fast and easily. This feature is supported in SQL Server 2012 and later versions. It started as a SQL Server Express feature.

包含的另一个工具是SqlLocalDB。开发人员可使用此工具安装轻型SQL Server，该服务器可在几分钟内轻松安装。要安装SQL Server实例，您需要SQL Installer并配置几个选项。使用SqlLocalDB，您可以快速轻松地拥有一个实例。 SQL Server 2012和更高版本支持此功能。它最初是作为SQL Server Express功能启动的。

To run the help, just write SqlLocalDB -? or SqlLocalDB /?

要运行帮助，只需编写SqlLocalDB-？还是SqlLocalDB /？

To list the instances available, write SqlLocalDB info:

要列出可用的实例，请编写SqlLocalDB info：

To create a SqlLocalDB instance, write SqlLocalDB create and specify the name of the instance using quotes:

要创建SqlLocalDB实例，请编写SqlLocalDB create并使用引号指定实例名称：

In SQL Server Management Studio, you can connect to the instance using the syntax (localdb)\instancename:

在SQL Server Management Studio中，可以使用语法（localdb）\ instancename连接到实例：

Once connected, you can work as a normal database:

连接后，您可以作为普通数据库工作：

The restrictions are the following:

限制如下：

LocalDB does not support the FILESTREAM feature.
LocalDB不支持FILESTREAM功能。
The instance cannot be a subscriber in a merge replication.
该实例不能是合并复制中的订阅服务器。
Only local queues are supported in Service Brokers.
Service Broker中仅支持本地队列。

For more information about LocalDB, we have 2 nice articles written by Marko Zivkovic:

有关LocalDB的更多信息，我们有2篇由Marko Zivkovic撰写的不错的文章：

结论 (Conclusion)

In this article, we presented the following tools:

在本文中，我们介绍了以下工具：

SQLdiag
SQLdiag
osql
osql
LocalDB
本地数据库

SQLdiag is used to troubleshoot problems related to SQL Server. It connects information about counters, logs, errors, traces, etc.

SQLdiag用于解决与SQL Server有关的问题。它连接有关计数器，日志，错误，跟踪等的信息。

The other tool is osql. This tool may be useful if you need to migrate old SQL Server information without installing extra tools. For example, I used osql to create a database backup in SQL Server 2000, because the Enterprise Manager (the old SSMS) was corrupt in an old server for the government in my country.

另一个工具是osql。如果您需要迁移旧SQL Server信息而无需安装其他工具，则此工具可能很有用。例如，我使用osql在SQL Server 2000中创建数据库备份，因为企业管理器（旧的SSMS）在我国*的旧服务器中已损坏。

LocalDB can be used by developers if they do not want to install an instance of SQL Server and want to optimize resources to start using SQL Server.

如果开发人员不想安装SQL Server实例并希望优化资源以开始使用SQL Server，则可以使用LocalDB。

翻译自: https://www.sqlshack.com/sql-server-utilities-you-probably-didnt-know-about-sqldiag-osql-and-sqllocaldb/