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

2023-10-18 阅读 27 评论 0

摘要：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: SQL server，在本文中，我們將解釋

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:

SQL server，在本文中，我們將解釋以下工具：

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

jdbc sqlserver？ 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參數指定另一個文件夾）：

Sql server？ 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-?，您可以獲得有關參數和命令使用的幫助：

sqlserver2012使用教程、

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

為了開始，在cmd中，編寫SQLdiag：

odbc driver 17 for sql server？

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撰寫的不錯的文章：

How to install Microsoft SQL Server Express LocalDB 如何安裝Microsoft SQL Server Express LocalDB
How to connect and use Microsoft SQL Server Express LocalDB 如何連接和使用Microsoft SQL Server Express LocalDB

結論 (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。