基于sqlcmd命令行工具管理SQL server

深碍√TFBOYSˉ_ 2022-08-21 05:42 206阅读 0赞

> 在SQLServer数据库当中，除了大家熟知的基于SSMS来管理SQLserver数据库之外，还有一个很强大的命令行工具sqlcmd。该命令行工具基本等同于Oracle SQL\*Plus以及 MySQL命令提示符下以实现相关的运维管理工作。尤其是需要多个脚本执行的时候，sqlcmd便派上用场了。本文描述了sqlcmd的一些常规用法以及给出如何通过批处理方式执行脚本的示例。

## 一、获取sqlcmd帮助 ##

C:\>sqlcmd -?
    Microsoft (R) SQL Server Command Line Tool
    Version 12.0.2000.8 NT   %当前版本为SQLserver2014 12.0%
    Copyright (c) 2014 Microsoft. All rights reserved.
    
    usage: Sqlcmd            [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-N Encrypt Connection][-C Trust Server Certificate] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-z new password] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit] [-k[1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p[1] print statistics[colon format]] [-R use client regional setting] [-K application intent] [-M multisubnet failover] [-b On error batch abort] [-v var = "value"...] [-A dedicated admin connection] [-X[1] disable commands, startup script, environment variables [and exit]] [-x disable variable substitution] [-? show syntax summary]

## 二、最常用的选项 ##

服务器选项（-S），用于标识 sqlcmd 连接到的 Microsoft SQL Server 实例。
    身份验证选项（-E、-U 和 -P），用于指定 sqlcmd 连接到 SQL Server 实例所使用的凭据。-E 选项为默认选项，毋须指定。
    
    输入选项（-Q、-q 和 -i），用于标识 sqlcmd 输入的位置。
    输出选项 (-o)，用于指定 sqlcmd 输出所在的文件。

## 三、常见用法 ##

使用 Windows 身份验证连接到默认实例，以交互方式运行 Transact-SQL 语句：
            sqlcmd -S <ComputerName>
    
    上述示例中，未指定 -E，因为它是默认选项，而且 sqlcmd 使用 Windows 身份验证连接到默认实例。
    
    使用 Windows 身份验证连接到命名实例，以交互方式运行 Transact-SQL 语句：
            sqlcmd -S <ComputerName>\<InstanceName>  或者  sqlcmd -S .\<InstanceName>
    
    
    使用 Windows 身份验证连接到命名实例，并指定输入和输出文件：
            sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>
    
    使用 Windows 身份验证连接到本地计算机上的默认实例，执行查询，并在查询运行完毕后使 sqlcmd 保持运行状态：
            sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"
    
    使用 Windows 身份验证连接到本地计算机上的默认实例，执行查询，将输出定向到某个文件，并在查询运行完毕后使 sqlcmd 退出：
            sqlcmd -Q "SELECT * FROM AdventureWorks2012.Person.Person" -o MyOutput.txt
    
    使用 SQL Server 身份验证连接到命名实例，以交互方式运行 Transact-SQL 语句，并由 sqlcmd 提示输入密码：
            sqlcmd -U MyLogin -S <ComputerName>\<InstanceName>

## 四、交互用法 ##

交互方式，在请在未使用 -Q、-q、-Z 或 -i 选项指定任何输入文件或查询的情况下运行实用工具。
            例如：sqlcmd -S <ComputerName>\<InstanceName>
    
    交互方式2个常用的命令
            GO + Enter   : 将语句发送到SQLserver服务器并执行
            Exit 或 QUIT : 退出sqlcmd命令行工作方式
            :REST        : 清除语句缓存，键入 ^C 将使 sqlcmd 退出,在发出 GO 命令后，还可以用 ^C 停止语句缓存的执行。
            :ED          : 使用编辑器编写SQL
    
    示例      
            C:\>sqlcmd -U sa -P Sqlserve -H HQ1636
            1> use testdb;
            2> go
            已将数据库上下文更改为 'testdb'。
            1> select * from t2;
            2> go
            id          id2         ename
            ----------- ----------- -------------------
                      1           1 NULL
                      1        NULL NULL
                      1           2 John
    
            (3 rows affected)
            1> exit

## 五、使用sqlcmd运行SQL脚本 ##

> 这个是比较管用的。对于熟悉Oracle SQL\*Plus或者MySQL命令行的童鞋来说，有这个工具执行脚本，尤其是多个脚本需要执行的情绪，那个爽啊，不说了，直接看用法。

## 1、执行单个脚本 ##

脚本内容如下
            C:\>type E:\temp\Testsql.sql
            USE testdb;
            GO
            SELECT * FROM t2;
            GO
    
            执行脚本
            C:\>sqlcmd -U sa -P Sqlserve -H HQ1636 -i E:\temp\Testsql.sql -o E:\temp\Testresult.txt
    
            C:\>type E:\temp\Testresult.txt
            已将数据库上下文更改为 'testdb'。
            id          id2         ename
            ----------- ----------- --------------------
                      1           1 NULL
                      1        NULL NULL
                      1           2 John
    
            (3 rows affected)

## 2、通过专用管理连接使用sqlcmd ##

下面使用专用连接方式杀死特定的session
            C:\>sqlcmd -U sa -P Sqlserve -H HQ1636 -A
            1> SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id<>0;
            2> go
            blocking_session_id
            -------------------
                             54
    
            (1 rows affected)
            1> kill 54;
            2> go

## 3、使用 sqlcmd 执行存储过程 ##

C:\>type E:\temp\TestProc.sql
            CREATE PROC proc_query_t2 @ename VARCHAR(20)
            AS
                SELECT  *
                FROM    t2
                WHERE   ename = @ename;
    
            GO
    
            C:\>sqlcmd -U sa -P Sqlserve -H HQ1636 -i E:\temp\TestProc.sql
    
            C:\>sqlcmd -U sa -P Sqlserve -H HQ1636
            1> :setvar ename robin
            1> exec testdb.dbo.proc_query_t2 $(ename)
            2> go
            id          id2         ename
            ----------- ----------- --------------------
                      1           1 Robin
    
            (1 rows affected)

## 4、使用 sqlcmd 进行数据库日常管理 ##

C:\>type E:\temp\DB_bak.sql
            USE master;
            GO
            BACKUP DATABASE [$(db)] TO DISK='$(bakfile)'
    
            C:\>sqlcmd -U sa -P Sqlserve -H HQ1636
            1> :setvar db testdb
            1> :setvar bakfile e:\temp\testdb01.bak
            1> :r e:\temp\DB_bak.sql
            已将数据库上下文更改为 'master'。
            1> go
            已为数据库 'testdb'，文件 'testdb' (位于文件 1 上)处理了 368 页。
            已为数据库 'testdb'，文件 'testdb_log' (位于文件 1 上)处理了 5 页。
            BACKUP DATABASE 成功处理了 373 页，花费 0.377 秒(7.729 MB/秒)。

## 5、sqlcmd 对多个实例执行代码 ##

2> :connect 192.168.1.194 -U robin -P xx
            Sqlcmd: Successfully connected to server '192.168.1.194'.
            1> select getdate()
            2> go
    
            -----------------------
            2016-03-17 13:31:16.390
    
            (1 rows affected)
            1> :connect 192.168.1.207,2433 -U sa -P 123
            Sqlcmd: Successfully connected to server '192.168.1.207,2433'.
            1> select getdate()
            2> go
    
            -----------------------
            2016-03-17 13:32:25.787
    
            (1 rows affected)

## 6、使用批处理方式执行任务 ##

这个对于运维的童鞋来说实在是幸福，可以将脚本封装到批处理.bat文件以及加到windows计划任务。
            C:\>type e:\temp\batch.bat
            @echo off
            sqlcmd -U sa -P Sqlserve -H HQ1636 -i e:\temp\all.sql -b -o e:\temp\out.log
    
            C:\>type e:\temp\all.sql
            :r e:\temp\driver.sql
            :r e:\temp\hostinfo.sql
    
            C:\>type e:\temp\hostinfo.sql
            PRINT 'Below is host info.';
            PRINT '=================================';
    
            USE [master];
            GO
            EXEC xp_msver;
            GO
    
            C:\>type e:\temp\driver.sql
            PRINT 'Below is drive info.';
            PRINT '=================================';
            USE master;
            GO
            EXEC xp_fixeddrives;
            GO
    
            C:\>e:\temp\batch.bat    %执行批处理脚本%
    
            Below is drive info.
            =================================
            已将数据库上下文更改为 'master'。
            drive MB 可用空间    
            ----- -----------
            C           99784
            D          138623
            E           26783
            F          217172
    
            (4 rows affected)
            Below is host info.
            =================================
            已将数据库上下文更改为 'master'。
            Index  Name                             Internal_Value Character_Value                                           
            ------ -------------------------------- -------------- --------------------------------------------------
                 1 ProductName                                NULL Microsoft SQL Server                                      
                 2 ProductVersion                           786432 12.0.2000.8                                               
                 3 Language                                   2052 中文(简体，中国)                                          
                 4 Platform                                   NULL NT x64                                                    
                 5 Comments                                   NULL SQL                                                       
                 6 CompanyName                                NULL Microsoft Corporation                                     
                 7 FileDescription                            NULL SQL Server Windows NT - 64 Bit                            
                 8 FileVersion                                NULL 2014.0120.2000.08 ((SQL14_RTM).140220-1752)               
                 9 InternalName                               NULL SQLSERVR                                                  
                10 LegalCopyright                             NULL Microsoft Corp. All rights reserved.                      
                11 LegalTrademarks                            NULL Microsoft SQL Server is a registered trademark            
                12 OriginalFilename                           NULL SQLSERVR.EXE                                              
                13 PrivateBuild                               NULL NULL                                                      
                14 SpecialBuild                          131072008 NULL                                                   
                15 WindowsVersion                        131072008 6.1 (7601)                                             
                16 ProcessorCount                                4 4                                                      
                17 ProcessorActiveMask                        NULL                f                                       
                18 ProcessorType                              8664 NULL                                                   
                19 PhysicalMemory                            16297 16297 (17088618496)                                    
                20 Product ID                                 NULL NULL