1. SQL Server

a. Bcp

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

b. Dta

Used to analyze a workload and recommend physical design structures to optimize server performance for that workload.

c. SQLCMD

Allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.

How to use scripting variables with SQLCMD:

Option 1 - Variable is defined in the sql script file, but the variable value are resolved at the execution time

-- an input file: c:\BackupDatabase.sql

BACKUP DATABASE $(DatabaseToBackup) TO DISK ='C:\$(DatabaseToBackup).BAK'

--At the commmand prompt, execute the following:

sqlcmd -S (LOCAL) -E - i "C:\BackupDatabase.sql" -v DatabaseToBackup = model

Option 2 - Using the setvar command interactively

The following example shows how to set a scripting variable interactively by using the setvar command.

sqlcmd

:setvar MYDATABASE AdventureWorks2012

USE $(MYDATABASE);

Here is the result set.

Changed database context to 'AdventureWorks2012'

d. Osql

Allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.

e. SQLDiag

Used to collect diagnostic information for Microsoft Customer Service and Support.

f. Sqllogship

Used by applications to perform backup, copy, and restore operations and associated clean-up tasks for a log shipping configuration without running the backup, copy, and restore jobs.

g. sqllocalDB

An execution mode of SQL Server targeted to program developers.

h. Sqlmaint

Used to execute database maintenance plans created in previous versions of SQL Server.

i. Profiler

Used to start SQL Server Profiler from a command prompt.

j. Sqlagent90

Used to start SQL Server Agent from a command prompt.

k. Sqlps

Used to run PowerShell commands and scripts. Loads and registers the SQL Server PowerShell provider and cmdlets.

l. sqlserver

Used to start and stop an instance of Database Engine from the command prompt for troubleshooting.

m. Ssms

Used to start SQL Server Management Studio from a command prompt.

n. Tablediff

Used to compare the data in two tables for non-convergence, which is useful when troubleshooting a replication topology.

o. Dcexec

DCEXEC is used for the Management Data Warehouse as a part of the collector sets that actually grabs performance metrics and pumps it to a management data warehouse for reporting.

p. SQLIOSIM and SQLIO

Both are used together for stress testing on I/O level prior to the installation of SQL Server.

q. Sqldumper

You can use the Sqldumper.exe utility to generate a dump file on demand for any Microsoft Windows application. For example, you can generate a dump file for debugging an application problem when a computer that is running SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2008 R2, or Microsoft SQL Server 2012 is not responding to user requests. A dump file can be a mini-dump file, a full dump file, or a filtered dump file.

The SQL Server process calls the Sqldumper.exe utility internally to generate a dump file when the process experiences any exceptions. SQL Server passes flags to the Sqldumper.exe utility. You can use trace flags to change the flags that SQL Server passes to the utility in the context of an exception or in the context an assertion. These trace flags are in the range from 2540 to 2559. You can use these trace flags to generate certain types of dump files. For example:

· Trace Flag 2551: Produces a filtered memory dump

· Trace Flag 2544: Produces a full memory dump

· Trace Flag 2546: Dumps all threads for SQL Server in the dump file

· Trace Flag 8026: SQL Server will clear a dumptrigger after generating the dump once

If two or more trace flags are active, the option indicating the largest memory dump will be honored. If trace flags 2551 and 2544 are used, SQL Server will create a full memory dump.

How to obtain a Microsoft Windows application process identifier

To generate a dump file by using the Sqldumper.exe utility, you must have the process identifier of the Windows application for which you want to generate a dump file. To obtain the process identifier, follow these steps:

1. Press CTRL+ALT+DELETE, and then click Task Manager.

2. In the Windows Task Manager dialog box, click the Processes tab.

3. On the View menu, click Select Columns.

4. In the Select Columns dialog box, click to select the PID (Process Identifier) check box, and then click OK.

5. Notice the process identifier of the Windows application for which you want to generate a dump file. For the SQL Server application, notice the process identifier of the Sqlservr.exe process.

6. Close Task Manager.

You can also obtain the process identifier of the SQL Server application that is running on your computer by using the SQL Server error log file. For example, part of the SQL Server error log file resembles the following:

2006-04-18 09:53:03.94 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-04-18 09:53:03.94 Server (c) 2005 Microsoft Corporation.
2006-04-18 09:53:03.94 Server All rights reserved.
2006-04-18 09:53:03.94 Server Server process ID is 3716.

The number that appears after Server process ID is the process identifier for the Sqlservr.exe process.

How to run the Sqldumper.exe utility

Run the Sqldumper.exe utility under the context of the folder where SQL Server originally installed the utility. By default, the installation path of the Sqldumper.exe utility is as follows:

SQLServerInstallDrive:\Program Files\Microsoft SQL Server\90\Shared\SQLDumper.exe

Note SQLServerInstallDrive is a placeholder for the drive where you installed SQL Server 2005.

To generate a dump file by using the Sqldumper.exe utility, follow these steps:

1. Open the following folder:

SQLServerInstallDrive:\Program Files\Microsoft SQL Server\number\Shared

Note In this folder path, number is a placeholder for one of the following:

o The folder name is 110 for SQL Server 2012.

o The folder name is 100 for SQL Server 2008.

o The folder name 90 for SQL Server 2005.

2. Make sure that the Dbghelp.dll file is in this folder.

3. Click Start, click Run, type cmd, and then click OK.

4. At the command prompt, type the following command, and then press ENTER:

cd SQLServerInstallDrive:\Program Files\Microsoft SQL Server\number\Shared

Note In this folder path, number is a placeholder for one of the following:

o The folder name is 110 for SQL Server 2012.

o The folder name is 100 for SQL Server 2008.

o The folder name 90 for SQL Server 2005.

5. To generate a specific kind of dump file, type the corresponding command at the command prompt, and then press ENTER:

o Full dump file

Sqldumper.exe ProcessID 0 0x01100

o Mini-dump file

Sqldumper.exe ProcessID 0 0x0120

o Mini-dump file that includes indirectly referenced memory

Sqldumper.exe ProcessID 0 0x0120:40

o Filtered dump file

Sqldumper.exe ProcessID 0 0x8100

6. Note ProcessID is a placeholder for the process identifier of the Windows application for which you want to generate a dump file

If the Sqldumper.exe utility runs successfully, the utility generates a dump file in the folder where the utility is installed.

The dump file that the Sqldumper.exe utility generates has a file name pattern that resembles the following:

SQLDmprxxxx.mdmp

In this pattern, xxxx is an increasing number that is determined based on other files that have a similar file name in the same folder. If you already have files in the folder that have file names in the specified pattern, you may have to compare the date and the time that the file was created to identify the file that you want.

r. Ssbdiagnose

The ssbdiagnose utility reports issues in Service Broker conversations or the configuration of Service Broker services. Configuration checks can be made for either two services or a single service. Issues are reported either in the command prompt window as human-readable text, or as formatted XML that can be redirected to a file or another program.

s. Sac

Discontinued in SQL Server 2008. The functionality was replaced by policy-based management and SQL Server Configuration Manager

2. SSAS

a. Microsoft.AnalysisServices.Deployment

The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT). The input files are easily modifiable to customize the deployment of an Analysis Services project. The generated deployment script can then either be immediately run or saved for later deployment.

Microsoft.AnalysisServices.Deployment [ASdatabasefile]

{[/s[:logfile]] | [/a] | [[/o[:output_script_file]] [/d]]}

The following example shows how to deploy an Analysis Services project in silent mode, logging progress and error messages for later review:

Microsoft.AnalysisServices.Deployment.exe

<drive>:\My Documents\Visual Studio 2010\Projects\AdventureWorksProject\Project1\bin

/s: C:\ My Documents\Visual Studio 2010\Projects\AdventureWorksProject\Project1\bin\deployment.log

b. ascmd

The ascmd command-line utility enables a database administrator to execute an XMLA script, MDX query, or DMX statement against an instance of Microsoft SQL Server 2005 Analysis Services (SSAS). This command-line utility contains functionality for Analysis Services that resembles the sqlcmdutility included with SQL Server 2005. For more information, see the topic sqlcmd Utility in SQL Server 2005. The execution results of the script, query, or statement can be stored in a file together with relevant SQL Server Profiler trace information. The default install location for the ascmd command-line utility is as follows:

<system_drive>\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd

Example: - Querying an Analysis Services Cube

In this scenario, you create an input file that contains an MDX query (the query.mdx file) that contains user-defined scripting variable (cube) in the MDX query. You then call this input file from the ascmd command-line utility and specify a value for this variable at the command-line by using the–v parameter.

query.mdx file:

/* THIS IS AN MDX COMMENT */

SELECT [Measures].[Internet Sales.Amount] ON COLUMNS

FROM $(cube)

WHERE [Customer].[Country].&[United States]

Command-line example:

C:\>ascmd -S myserver -d "Adventure Works DW" -i query.mdx -o result.xml -v cube="[Adventure Works]"

c. asinstancerename

You can rename an existing instance of Microsoft Analysis Services by using the Rename Instance dialog box.

Security Note

While renaming the instance, the Analysis Services Instance Rename tool runs under elevated privileges, updating the Windows service name, security accounts, and registry entries associated with that instance. To ensure that these actions are performed, be sure to run this tool as a local system administrator.

The Analysis Services Instance Rename tool does not modify the program folder that was created for the original instance. Do not modify the program folder name to match the instance you are renaming. Changing a program folder name can prevent Setup from repairing or uninstalling the installation.

Note

The Analysis Services Instance Rename tool is not supported for use in a cluster environment.

To rename an instance of Analysis Services

1. Launch the Instance Rename tool, asinstancerename.exe, from C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio.

2. In the Rename Instance dialog box, in the Instance to rename list, select the instance that you want to rename.

3. In the New instance name box, enter the new name for the instance.

4. Verify that the user name and password are correct, and then click Rename.

The Analysis Services instance will be stopped and restarted as part of the name change.

Post-rename checklist

1. To resume access to databases that are running on the renamed instance, you will need to manually update the data connections in Excel or other client applications. Also check any predefined connections, such as Reporting Services shared data sources, Excel ODC files, or BI Semantic Model connection files that might reference the instance you just renamed. For more information, see Connect to Analysis Services.

2. Update PowerShell scripts or AMO scripts that you routinely use to backup, synchronize, or process databases.

3. Update project properties for Analysis Services projects that you work with in SQL Server Data Tools. For tabular mode server instances, be sure to update the Workspace Server property on the model.bim file, as well as the Server property on the project.

4. Depending on how you specified the service account, you might need to update database logins or file permissions that grant data access rights to the service (for example, if you use the service account to process data or access linked objects on another server).

Updating a database login or file permissions will be necessary if you used a virtual account to provision the service. Virtual accounts are based on the instance name, so if you rename the instance, the virtual account is also updated at the same time. This means that any previous logins or permissions that you created for the previous instance are no longer valid.

The following example provides an illustration. Suppose you installed a tabular mode server as an instance named “Tabular” using the default virtual account, resulting in the following configuration:

1. Instance name = <server>\TABULAR

2. Service name = MSOLAP$TABULAR

3. Virtual account = NT Service\ MSOLAP$TABULAR

Now suppose you rename the instance to “TAB2”. As a result of the name change, your configuration would now look like the following:

4. Instance name = <server>\TAB2

5. Service name = MSOLAP$TAB2

6. Virtual account = NT Service\ MSOLAP$TAB2

As you can see, database and file permissions that were previously granted to “NT Service\ MSOLAP$TABULAR” are no longer valid. To ensure that tasks and operations performed by the service run as before, you would now need to grant new database and file permissions to “NT Service\ MSOLAP$TAB2”.

3. SSIS

a. DTExec and DTExecUI

Used to configure and execute an Integration Services package. A user interface version of this command prompt utility is called DTExecUI, which brings up the Execute Package Utility.

b. dtutil

Used to manage SSIS packages.

c. Dtsinstall

Used to run the deployment manifest file of an SSIS package when SSIS 2008 or SSIS 2008 R2 is not installed

d. DTSWizard

To import or export data by using the SQL Server Import and Export Wizard

e. ISDeploymentWizard

To deploy a project to the Integration Services Server

f. SSISUpgrade.exe

To run the SSIS Package Upgrade Wizard from the command prompt.

To run the wizard from SQL Server Data Tools

At the command prompt, run the SSISUpgrade.exe file from the C:\Program Files\Microsoft SQL Server\110\DTS\Binn folder.

On the Journey to be a SQL Server Professional

SQL Server Command Line Utilities

1. SQL Server

2. SSAS

3. SSIS

4. SSRS