Command-Line Utilities

Command Utilities provides database administrators different way to access the databse engine. SQL Server 2005 provides new command line utilities such as:
DTA
TableDiff
sac: which can be used to import or export settings that relates to surface area configuration.
sqlcmd: Lets you execute T-SQL statements and scripts from the command prompt or from query editor window is SSMS. sqlcmd provides a mechanism for utilizing variables within sqlcmd input files or scripts.

To demostrate the use of scripting variables, lets use this example.

A new input file named backupAdventureWorks.sql is created with the following sql statements inside it.

BACKUP DATABASE $(DatabasetoBackup)
TO DISK ='c:\$(DatabasetoBackup).bak'

Scripting varaibles are refrenced using the $() designators. The -v option is used to assign a value to a variable at the command prompt as shown in the following example, which backups Adventureworks database.

Now execute the following in a command prompt.

sqlcmd -S pavilion -E -i"c:\backupAdventureWorks.sql" -v DatabasetoBackup = AdventureWorks

Scripting variables can also be assigned within the script using :SETVAR command. The input file from the previous backup would be modified as follows to assign the DatabasetoBackup variable within the script:


:SETVAR DatabasetoBackup AdventureWorks
BACKUP DATABASE $(DatabasetoBackup)
TO DISK ='c:\$(DatabasetoBackup).bak'

Then execute the following in a command prompt.

sqlcmd -S pavilion -E -i"c:\backupAdventureWorks.sql"

There are also other command-line utilities that are also new to SQL Server 2005 that are used to lauch the graphical tools that come with SQL Server.

• profiler90: Lauches the SQL Server Profiler application.
• sqlwb: This utility launches the SQL Server Management Studion(SSMS).
• dtswizard: This util luanches import/export wizard that allows you to import and export data between SQL Server data sources and other data sources.

DTA Command-Line Utility

dta is the command line version of the graphical Database Engine TunningAdvisory. They both provide performance recommendations based on the workload provided to them.
The workload to tune would either be a workload file or a workload table. The workload filemust be either a profiler trace file or an SQL script or SQL Server trace file(.log).
We will use the T-SQL below which is stored in a workload file named c:\dtascript.sql.


USE AdventureWorks
Go
Select * from HumanResources.Employee
where gender = 'M'

To demonstrate the use of dta, lets look at this example.

C:\>dta -S pavilion -E -D AdventurewORKS -if c:\dtascript.sql -s Mysession -of c:\dtaOutputscript.sql -F

Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utilityVersion 9.00.3042.00Copyright (c)
Microsoft Corporation. All rights reserved.

Tuning session successfully created. Session ID is 6.
Total time used: 00:00:01Workload consumed: 100%, Estimated improvement: 47%

Tuning process finished.Successfully generated recommendations script: c:\dtaOutputscript.sql.

The output of the dtaoutputscript.sql will contain the following performance recommendation:

use [AdventureWorks]
go
CREATE NONCLUSTERED INDEX [_dta_index_Employee_14_869578136__K9_1_2_3_4_5_6_7_8_10_11_12_13_14_15_16] ON [HumanResources].[Employee]
( [Gender] ASC)
INCLUDE ( [EmployeeID],
[NationalIDNumber],
[ContactID],[LoginID],
[ManagerID],[Title],[BirthDate],
[MaritalStatus],
[HireDate],
[SalariedFlag],
[VacationHours],
[SickLeaveHours],
[CurrentFlag],
[rowguid],
[ModifiedDate]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

The dta ouput recommends that a new non-clustered index be created on the Gender column in the Employee table.

TableDiff Command-Line Utility

Tablediff is an SQLCMD command utility that allows you to compare the data contained within two different tables. This was developed for replication when data in two tables should be the same.This tool can determine if the content of tables involve in replication are the same, and if they are different, it can identify what data in the tables are different.

Let me demostrate this with this simple example:

USE AdventureWorks
GO
Create table table1( id int identity(1,1) NOT NULL, name varchar(50) )GO
Create table table2( id int identity(1,1) NOT NULL, name varchar(50) )GO
insert into table1 values('Jesse')
insert into table1 values('Emmanuela')
GO
insert into table2 values('Jesse')
insert into table2 values('Emma')
GO
--Now run the tablediff util on the command line from this folder:C:\program files\microsoft sql server\90\com\tablediff.exe

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "pavilion" -sourcedatabase "AdventureWorks" -sourceschema "dbo" -sourcetable "table1" -destinationserver "pavilion\sql2005" -destinationdatabase "AdventureWorks" -destinationschema "dbo" -destinationtable "table2" -f c:\Tablediff_output.txt

-- The execution of the utility with the above parameters results in the following output to the command prompt window:

User-specified agent parameter values:
-sourceserver pavilion\sql2005
-sourcedatabase AdventureWorks
-sourceschema dbo-sourcetable table1
-destinationserver pavilion\sql2005
-destinationdatabase AdventureWorks
-destinationschema dbo
-destinationtable table2
-f c:\Tablediff_output.txt

Table [AdventureWorks].[dbo].[table1] on pavilion\sql2005 and Table [AdventureWorks].[dbo].[table2] on pavilion\sql2005 have 1 differences.Fix SQL written to c:\Tablediff_output.txt.sql.

Err id Col
Mismatch 2 name
The requested operation took 1.46875 seconds.

The output displays the summary and shows the comparison results. It found one difference between table1 and table2. The -f parameter caused tablediff utility to output a SQL file that can be used to fix the difference in the destination file.

The output of the SQL file is:

-- Host: pavilion\sql2005
-- Database: [AdventureWorks]
-- Table: [dbo].[table2]
SET IDENTITY_INSERT [dbo].[table2] ON
UPDATE [dbo].[table2]
SET [name]='Emmanuela' WHERE [id] = 2
SET IDENTITY_INSERT [dbo].[table2] OFF