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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 在SQLServer数据库当中,除了大家熟知的基于SSMS来管理SQLserver数据库之外,还有一个很强大的命令行工具sqlcmd。

在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  
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
11天前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5天前
|
SQL 存储 搜索推荐
SQL server增删改查(1)
SQL server增删改查(1)
10 0
|
5天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
31 2
|
5天前
|
SQL JSON atlas
实时计算 Flink版产品使用合集之SQL Server CDC是否支持抽取SQL Server视图
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
11天前
|
SQL 数据处理 API
实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
15 0
|
12天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
13天前
|
SQL XML Linux
SQL Server的版本
【5月更文挑战第14天】SQL Server的版本
27 3
|
13天前
|
SQL 关系型数据库 数据管理
Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
【5月更文挑战第14天】Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
24 2
|
13天前
|
SQL 存储 数据库连接
LabVIEW与SQL Server 2919 Express通讯
LabVIEW与SQL Server 2919 Express通讯
14 0
|
13天前
|
SQL Windows
安装SQL Server 2005时出现对性能监视器计数器注册表值执行系统配置检查失败的解决办法...
安装SQL Server 2005时出现对性能监视器计数器注册表值执行系统配置检查失败的解决办法...
17 4