SQL Server 2012 Utilities


SQL Server 2012 Utilities

Microsoft provided many Command line utilities for SQL Server 2012. Command line utilities are available for the following SQL Server features. Command line tools and utilities are very useful for automation, 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 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:

1)      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  day one i.e. SQL 6.5.

2)      dta: The dta utility is the command line version of Database Engine Tuning Advisor.

3)      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, much more.

1)      sqlcmd: The sqlcmd utility allows us to run T-SQL queries, scripts from command prompt. This utility replaced the recent osql.exe

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

1)      sqllogship: Utility used for shipping database logs from one SQL Server instance to another.

2)      SqlLocalDB: Utility to create an instance of Microsoft SQL Server 2012 Express LocalDB.

3)      sqlmaint: Used for creating Database maintenance like consistency check, reindex, reorg, backup, etc.

4)      sqlps: SQL Server PowersShell cmdlets. I like this tool, you can do a lot from this, efter all PowerShell, you know what I meant.

5)      sqlservr: Used when you want to start SQL in single user mode or when you want to move system database.

6)      Ssms: This is SQL Server Management Studio.

7)      tablediff: This utility is used for table comparison.

8)      Dcexec: SQL Server data collector tool used for collecting data from SQL server.

9)      SQLIOSim: This tool simulates I/O on SQL Server.

10)   Sqldumper: Name itself say much. 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. These utilities are as follows:

1)      Dtexec : This utility is used to execute SSIS packages.

2)      dtutil : This utility is provided by Microsoft for managing all SSIS packages, such as copy, move, delete, or verify the existence of a package.

3)      dtsinstall : Deployment utility using manifest file.

4)      DTSWizard: This is an import/export utility for SQL Server.

5)      ISDeploymentWizard: Deployment utility for Integration Service.

6)      SSISUpgrade: Utility to upgrade older versions of SSIS packages to a new version.

Reporting Services:

1)      rs: This is the tool used to run scripts against SSRS.

2)      rsconfig: This tool is used for configuring SSRS.

3)      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 website www.codeplex.com  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 go to books online or the MSDN website. Most of them are documented, better then never before.

Any your comments will be learning point for me, thank you.

Mehboob

MS SQL & SPS DBA

MCTS & MCITP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s