SQL Server 2012 Command Line Utilities

Microsoft provided many Command line utilities for SQL Server 2012.  In this article I am going to explain the functionality and use of these command line utilities based on the feature it is dealing with.

Command line utilities are available for the following SQL Server features. Command line tools and utilities are very useful for automation.

Out of the box, you will get certain command line utilities.

• SQL Server Database Engine
  Bcp, dta, , osql, profiler, sqlcmd, sqldiag, sqllogship, sqllocaldb, sqlmaint
  Sqlps, sqlserver, ssms, tablediff, dcexec, sqliosim, sqldumper, SqlWtsn
• SQL Server Agent
  SQLAgent
• SQL Server Integration Services
  Dtexec, dtutil, dtsinstall, DTSWizard, ISDeploymentWizard, SSISUpgrade.exe
• SQL Server Analysis Services
  ASInstanceRename
  Microsoft.AnalysisServices.Deployment
• SQL Server Service broker
  Ssbdiagnose
• SQL Server Reporting Services
  Rs, rsconfig, rskeymgmt

Database Engine

Microsoft provided specific command line utilities that will work with the database engine. Most of them were available even on the older versions of SQL Server and you may be familiar with those. These utilities are as follow:

• bcp: The bcp utility is used for importing and exporting to and from SQL Server to flat files and vice versa. This utility is been around since SQLServer 6.5.
• dta: The dta utility is the command line version of Database Engine Tuning Advisor.
• osql: The osql utility allows us to run T-SQL queries, scripts from command prompt. This will be deprecated in future versions. This is available for backwards compatibility only. Use SQLCMD instead.
• Profiler: This launches SQL Server Profiler. There are many optional parameters allowed, very helpful scheduling, etc.
• sqlcmd: The sqlcmd utility allows us to run T-SQL queries, scripts from command prompt. This utility replaced the old isql.exe and the recent osql.exe
• SQLdiag: SQL Diagnostic utility can be used to collect logs and data files from SQL Server. It can also be used to monitor SQL servers and troubleshoot specific problems.

  Related Articles

  • The Format() Function in SQL Server 2012
  • Sequence Object in SQL Server 2012
  • Contained Database Authentication in SQL Server 2012
  • Analysis Services PowerShell Provider (SQLAS) in SQL Server 2012

  
  SQLDIAG

• sqllogship: Utility used for shipping database logs from one SQL Server instance to another.
• SqlLocalDB: Utility to create an instance of Microsoft SQL Server 2012 Express LocalDB.
• sqlmaint: Used for creating Database maintenance like consistency check, reindex, reorg, backup, etc.
• sqlps: SQL Server PowersShell cmdlets.
• sqlservr: Used when you want to start SQL in single user mode or when you want to move system database.
• Ssms: This is SQL Server Management Studio. You can kick off Ssms with many parameters.

  
  SQL Server Management Studio

• tablediff: This utility is used for table comparison.
• Dcexec: SQL Server data collector tool used for collecting data from SQL server.
• SQLIOSim: This tool simulates I/O on SQL Server.
• Sqldumper: Name says it all. This tool is used to generate a dump file on demand.

Integration Services

Microsoft provided specific command line utilities that will work with the integration service engine. Most of them were available even on older versions of SQL Server and you may be familiar with those. These utilities are as follows:

• Dtexec : This utility is widely used to execute SSIS packages.

  
  DTExec

• dtutil : This utility is provided by Microsoft for managing all SSIS packages. The tool can copy, move, delete, or verify the existence of a package.

  
  DTUtil

• dtsinstall : Deployment utility using manifest file.
• DTSWizard: This is an import/export utility for SQL Server.
• ISDeploymentWizard: Deployment utility for Integration Service.
• SSISUpgrade: Utility to upgrade older versions of SSIS packages to a new version.

  
  SSIS Package Upgrade Wizard

SQL Server Agent

• SQLAgent : SQL Server agent can be started from the command prompt by using –c switch.

Analysis Services

• ASInstanceRename: Utility for renaming the Analysis service instance.

Rename Instance

• Microsoft.AnalysisServices.Deployment: The Microsoft.AnalysisServices.Deployment utility helps you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt.

   

Reporting Services

• rs: This is the tool used to run scripts against SSRS.
• rsconfig: This tool is used for configuring SSRS.
• rskeymgmt: This tool is used for managing keys on SSRS.

Service Broker

• ssbdiagnose: Utility to run diagnostic on SQL server service broker.

Other Command Line Options

There are other command line utilities available for all the flavors of SQL Server 2012. You can download some from the Codeplex website or you can develop on your own using scripting language like PowerShell, batch file, etc.

Note: if you want to know all of the arguments that are accepted in a command line utility, execute that utility either by passing /? Or -? as a parameter. If you need more details of any utilities or want to see an example, you can always search in books online or the MSDN website. Most of them are documented.

Conclusion

As mentioned in the beginning of this article I have explained the functionality and use of these command line utilities based on the feature it is dealing with.

See all articles by MAK