SQL Server -- 基于sqlcmd命令行工具管理SQL Server

vincen

　　基于sqlcmd命令行工具管理SQL Server
　　From: http://blog.csdn.net/leshami/article/details/50913475
　　在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>　　[-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>
　　[-a packetsize]        [-e echo input]        [-I Enable Quoted>　　[-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  | i:[,o:]] [-Z new password and exit]
　　[-k[1|2] remove[replace] control characters]

　　[-y variable length type display>
　　[-Y fixed length type display>　　[-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]1
　　二、最常用的选项
　　服务器选项（-S），用于标识 sqlcmd 连接到的 Microsoft SQL Server 实例。
　　身份验证选项（-E、-U 和 -P），用于指定 sqlcmd 连接到 SQL Server 实例所使用的凭据。-E 选项为默认选项，毋须指定。
　　输入选项（-Q、-q 和 -i），用于标识 sqlcmd 输入的位置。
　　输出选项 (-o)，用于指定 sqlcmd 输出所在的文件。
　　三、常见用法
　　使用 Windows 身份验证连接到默认实例，以交互方式运行 Transact-SQL 语句：
　　sqlcmd -S
　　上述示例中，未指定 -E，因为它是默认选项，而且 sqlcmd 使用 Windows 身份验证连接到默认实例。
　　使用 Windows 身份验证连接到命名实例，以交互方式运行 Transact-SQL 语句：
　　sqlcmd -S \  或者  sqlcmd -S .\
　　使用 Windows 身份验证连接到命名实例，并指定输入和输出文件：
　　sqlcmd -S \ -i  -o
　　使用 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 \1
　　四、交互用法
　　交互方式，在请在未使用 -Q、-q、-Z 或 -i 选项指定任何输入文件或查询的情况下运行实用工具。
　　例如：sqlcmd -S \
　　交互方式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

　　>　　----------- ----------- -------------------
　　1           1 NULL
　　1        NULL NULL
　　1           2 John
　　(3 rows affected)
　　1> exit 1
　　五、使用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'。

　　>　　----------- ----------- --------------------
　　1           1 NULL
　　1        NULL NULL
　　1           2 John
　　(3 rows affected)   1
　　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_id0;
　　2> go
　　blocking_session_id
　　-------------------
　　54
　　(1 rows affected)
　　1> kill 54;
　　2> go1
　　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

　　>　　----------- ----------- --------------------
　　1           1 Robin
　　(1 rows affected)1
　　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/秒)。1
　　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)1
　　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>