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.
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.
