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

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

一、获取sqlcmd帮助

C:>sqlcmd -？Microsoft （R） SQL Server Command Line ToolVersion 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 HQ16361> use testdb;2> go已将数据库上下文更改为 "testdb"。1> select * from t2;2> goidid2 ename----------- ----------- -------------------1 1 NULL1NULL NULL1 2 John（3 rows affected）1> exit

五、使用sqlcmd运行SQL脚本

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

1、执行单个脚本

脚本内容如下C:>type E:	empTestsql.sqlUSE testdb;GOSELECT * FROM t2;GO执行脚本C:>sqlcmd -U sa -P Sqlserve -H HQ1636 -i E:	empTestsql.sql -o E:	empTestresult.txtC:>type E:	empTestresult.txt已将数据库上下文更改为 "testdb"。idid2 ename----------- ----------- --------------------1 1 NULL1NULL NULL1 2 John（3 rows affected）

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

下面使用专用连接方式杀死特定的sessionC:>sqlcmd -U sa -P Sqlserve -H HQ1636 -A1> SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id<>0;2> goblocking_session_id------------------- 54（1 rows affected）1> kill 54;2> go

3、使用 sqlcmd 执行存储过程

C:>type E:	empTestProc.sqlCREATE PROC proc_query_t2 @ename VARCHAR（20）ASSELECT*FROMt2WHERE ename = @ename;GOC:>sqlcmd -U sa -P Sqlserve -H HQ1636 -i E:	empTestProc.sqlC:>sqlcmd -U sa -P Sqlserve -H HQ16361> :setvar ename robin1> exec testdb.dbo.proc_query_t2 $（ename）2> goidid2 ename----------- ----------- --------------------1 1 Robin（1 rows affected）

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

C:>type E:	empDB_bak.sqlUSE master;GOBACKUP DATABASE [$（db）] TO DISK="$（bakfile）"C:>sqlcmd -U sa -P Sqlserve -H HQ16361> :setvar db testdb1> :setvar bakfile e:	emp	estdb01.bak1> :r e:	empDB_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 xxSqlcmd: 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 123Sqlcmd: 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:	empatch.bat@echo offsqlcmd -U sa -P Sqlserve -H HQ1636 -i e:	empall.sql -b -o e:	empout.logC:>type e:	empall.sql:r e:	empdriver.sql:r e:	emphostinfo.sqlC:>type e:	emphostinfo.sqlPRINT "Below is host info.";PRINT "=================================";USE [master];GOEXEC xp_msver;GOC:>type e:	empdriver.sqlPRINT "Below is drive info.";PRINT "=================================";USE master;GOEXEC xp_fixeddrives;GOC:>e:	empatch.bat%执行批处理脚本%Below is drive info.=================================已将数据库上下文更改为 "master"。drive MB 可用空间----- -----------C 99784D138623E 26783F217172（4 rows affected）Below is host info.=================================已将数据库上下文更改为 "master"。IndexName Internal_Value Character_Value ------ -------------------------------- -------------- -------------------------------------------------- 1 ProductNameNULL Microsoft SQL Server 2 ProductVersion 786432 12.0.2000.83 Language 2052 中文（简体，中国） 4 Platform NULL NT x64 5 Comments NULL SQL6 CompanyNameNULL Microsoft Corporation7 FileDescriptionNULL SQL Server Windows NT - 64 Bit 8 FileVersionNULL 2014.0120.2000.08 （（SQL14_RTM）.140220-1752）9 InternalName NULL SQLSERVR10 LegalCopyright NULL Microsoft Corp. All rights reserved.11 LegalTrademarksNULL Microsoft SQL Server is a registered trademark12 OriginalFilename NULL SQLSERVR.EXE13 PrivateBuild NULL NULL14 SpecialBuild131072008 NULL 15 WindowsVersion131072008 6.1 （7601） 16 ProcessorCount4 417 ProcessorActiveMaskNULLf 18 ProcessorType8664 NULL 19 PhysicalMemory16297 16297 （17088618496）20 Product ID NULL NULL

本文永久更新链接地址