SQL Server Express Utility

Version 1.0.2130

Overview

SQL Server Express Utility is a tool for interacting with SQL Server. It provides many features including:

- Connect to the main instance or user-instance of SQL Server.

- Create/Attach/Detach/List databases on the server.

- Upgrade database files to match the version of the server.

- Execute SQL statements via the console (similar to SQLCMD) or the console window (UI).

- Retrieve the version of SQL Server running.

- Enable/Disable trace flags (e.g. to trace SQL statements sent to the server by any client app)

- List the instances of SQL Server on the local machine or on remote machines.

- Checkpoint and shrink a database

- Measure the performance of executing specific queries using the timer function (console mode).

- Create and playback lists of SQL commands to be executed by the server.

- Log all input/output.

Even though the tool was built with focus on SQL Server Express (by default, it tries to connect to the SQLEXPRESS instance on the local machine), the tool can also be used to connect to other versions of SQL Server, including SQL Server 2000 and earlier (see the –main option for more information).

Important note

Microsoft Corporation does not provide any technical or customer support services for SQL Server Express Utility

Major changes from the Beta2 version

- All help is now build into SSEUtil.exe. For help on a specific command type SSEUtil help <commandname>.

- Create new databases (-create command)

- List the child instances running (-childlist command)

- Interactive console window (-consolewnd command)  

- History commands in the SQL console (!history show/clear/save)

- Set the connection timeout (-timeout option)

- Set the command timeout (-commandtimeout) command

- Script files can contain variables that will be expanded before sending to the server.

- You can specify the name of the database when attaching (see -attach command)  

e.g. SSEUtil -attach c:\northwind.mdf NW

- Most commands can now work with a file path (.mdf) or a database name.

- Detach command syntax changed. You can provide either a path or name of DB to detach

Command summary

Microsoft (R) SQL Server Express Utility v1.0.2130

Copyright (C) Microsoft Corporation 2005. All rights reserved.

Usage: SSEUtil <command> [<args>] [<options>]

Commands

  -a[ttach] <dbpath> [<dbname>]

     Attach a database file to the server using the name if specified.

  -create <dbpath>|name=<dbname> [<dbname>]

     Create a new database given the database path or name.

  -l[ist]

     Lists all databases on the server.

  -d[etach] <dbpath[*]>|name=<dbname>

     Detaches database(s) by path or name.

  -u[pgrade] <dbpath>|<directory>

     Upgrades an individual database file or all database files in a folder.

  -t[race] +|-[<number>]

     Enables or disables the specified trace number for all client connections.

     Exclude number to trace SQL commands. Output written to the server log.

  -childlist

     Lists the child (user) instances of SQL Server.

  -c[onsole]

     Console mode. Allows user to type SQL statements to run on the server.

  -consolewnd

      Launches the interactive console window.

  -run <filepath> [<var1>=<val1>[,...]]

     Runs a command file (SQL or extended commands) with the variables provided.

  -version

     Displays the version reported by SQL Server.

  -listsrv [remote]

     Lists the local or remote instances of SQL Server.

  -shrink <dbpath>|name=<dbname>

     Shrinks the given database and runs checkpoint.

Options

  -m[ain]

     Use the main instance. (Default is to use the child instance.)

  -child [<username>]

     Connect to child instance for the current or specified user.

-s[erver] <server>[\<instance>][,<port>]

Specify the server, instance name and/or port to connect to.

     e.g. -s .\SQLEXPRESS will connect to SSE on the local machine.

  -user <username>

     Specify the user name to connect with.

  -pwd <password> | -pwd?

     Specify the password to connect with, or prompt for it.

     e.g. -s mysrv -user sa -pwd? will prompt for a password.

  -timeout <seconds>

     Specify the connection timeout in seconds. 0 for infinite.

  -cmdtimeout <seconds>

     Specify the command timeout in seconds. 0 for infinite.

  -log <logfile>

     Write all program input/output to the specified log file.

Command information

Here are some examples of the commands that SSEUtil offers. For more information, see the help in the tool itself by typing ‘SSEUtil help’ or ‘SSEUtil help <commandname>’ on the command line.

Creating databases (-create command).

            Use this command to create a new database or database file.

C:\>sseutil -create c:\northwind.mdf

Command completed successfully.

Attaching database files (-a command).

Attach a given database file to the server. This is done by using the ADO.NET/SQL AttachDbFileName support.

C:\>sseutil -a c:\northwind.mdf

Command completed successfully.

The command above will lets SQL Server generate a name for the database based on the file path. To specify a name explicitly, use:

C:\>sseutil -a c:\northwind.mdf nw

Command completed successfully.

In the example above, northwind.mdf will be attached under the name ‘nw’.

Listing databases (–l command).

List the databases on the server.

Note: Local databases attached via the AttachDBFile name functionality of ADO.NET have a name that is based on the file path as seen below at 5.

C:\>sseutil -l

1. master

2. tempdb

3. model

4. msdb

5. c:\northwind.mdf

Detaching databases (-d command).

Use this command to detach a database from the server. Detach takes the path of the data file to detach or a database name.

            Detach by partial path:

C:\>sseutil -d c:\mydata\*

Detached 'c:\mydata\northwind.mdf' successfully

Detached 'c:\mydata \pubs.mdf' successfully

            Detach by database name:

C:\>sseutil -d name=northwind

Detached 'c:\mydata\northwind.mdf' successfully

Upgrading database files (-u command).

In order to work properly with SQL Server, the data files created by previous versions might need to be upgraded. This command allows you to upgrade one or more files to the version required by the given server. The upgrade process is done entirely by the server.

C:\>sseutil -u c:\mydatabases

Processed 'northwind.mdf'

Enabling trace flags (-t command).

This command allows you to enable/disable certain trace flags on the server. Trace flags are always applied on all client connections as seen in the example below (-1 is added to all trace flags).

            Here is an example on how to enable tracing of SQL commands sent to the server by any client application:

C:\>sseutil -t +

Trace flags {4054,-1} were set to True

Typically, the trace output is written to the server log. For the user instance of SSE, this is under \documents and settings\local settings\application data\...\SQLEXPRESS. For the main instance, it is under the SQL installation path under LOG.

Retrieving the server version information (-version command).

This command will query the server for the version information. The output is the information directly as returned by the server.

C:\>sseutil -version

Microsoft SQL Server 2005 - 9.00.0000 (Intel X86)

        Oct 26 2005 20:39:57

        Copyright (c) 1988-2005 Microsoft Corporation

        Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Shrinking a database (-shrink command).

This command currently works by pointing it at a file path or specifying a database name. It’ll attach the given database, issue a shrink command to the server. The difference in size will be reported as follows:

C:\>sseutil -shrink c:\northwind.mdf

File                Initial size   Final size

------------------------------------------------

northwind.mdf       12032 KB        4352 KB

northwind_log.LDF   1024 KB         768 KB

Command completed successfully.

Listing the child (user) instances of a server (-childlist command).

C:\>sseutil –childlist

User            Pipe                                   ProcessId   Status

----------------------------------------------------------------------------

DOMAIN\USER     \\.\pipe\BC6A3A8C-EAC4-3E\tsql\query   1132        alive

Retrieving the list of SQL Server instances (-listsrv command).

This command allows the user to list the instances of SQL server either locally or on remote machines.

            -- Local --

This will list the instances of SQL Server installed on the local machine whether they are currently running or not.

C:\>sseutil -listsrv

MYMACHINE\SQLEXPRESS

      -- Remote --

This will list the instances of SQL Server running on remote machines. Note that the list might not be complete as this relies on a UDP broadcast techniques and not all servers might be able to reply withing the time allocated.

Gathering list of remote servers...

ABAL01

BELL01

CLIENT5\DB1

[...]

Enabling logging of all input/output (-log option)

            This command allows the user to have all input/output to the tool written to a specified log file.

sseutil -version -log c:\log.txt

It is also supported in the console via the !logopen and !logclose commands.

C:\>sseutil -c

Console mode. Type 'help' for more information.

1> !logopen c:\log.txt

Log started.

1> !list

1. master

2. tempdb

3. model

4. msdb

1> !logclose

Log stopped.

Connecting to the main instance (-main option)

By default, SSEUtil will try to connect to the user-instance. User-instances are instances of sqlservr.exe running under the current user’s account. This is done by adding the ‘User Instance=true’ to the ADO.NET connection string. In order to connect to the ‘main’ instance or service instance, use the –main switch. You’ll need to use that switch with connecting to older SKUs or to SKUs on which user instances are disabled.

            The following example will list all DBs on the main instance as opposed to the user instance:

sseutil -m -l

Connecting to a child (user) instance running under a different user name (-child option).

This command allows the user to connect to the user instance (child instance) that is running under a different user name. By default, SSEUtil will try to connect to the user instance for the current user.

The most common place where this can be useful is the Web scenario where the user instance of SSE runs under a different user name than the one currently logged in.

C:\>sseutil -l -child MYDOMAIN\username

Instance '\\.\pipe\2137BF9E-9128-5C\tsql\query' was found.

1. master

2. tempdb

3. model

4. msdb

5. c:\mydata.mdf

Connecting to a remote machine with password prompt (-s and –pwd[?] options)

SSEUtil allows you to specify the password in 2 ways.

1) Using the –pwd switch you can specify the password to use: -pwd hello

2) Using the –pdw? Switch you can let SSEUtil prompt you for password. This avoids displaying the password on the command line.

You can specify the server\instance name to use to connect using the –s switch.

C:\>sseutil -s imagine\Northwind -user sa -pwd? -m -c

Enter password for user 'sa': ***********

Console mode. Type 'help' for more information.

> 

 Console mode (-c command)

The console mode allows users to enter commands that will run against the server. Here’s some information about the console as reported by its help:

C:\dd>sseutil -c

Console mode. Type 'help' for more information.

The console supports 2 types of commands: (1) SQL, (2) Extended

SQL Commands

   SQL commands are sent directly to the server. Type the command(s)

   you want to send. Once all commands are entered, type GO [ENTER]

   on a line to send them to the server.

Extended commands

   Extended commands are interpreted by the console first.

   All extended commands are preceded with a '!' character

   !attach <dbpath> [<dbname>]

      Attaches a database file to the server using the name if specified.

   !create <dbpath>|name=<dbname> [<dbname>]

     Creates a new database given the database path or name.

   !detach <dbpath[*]>|name=<dbname>

      Detaches database(s) by path or name.

   !list

      Lists all databases on the server.

   !timer

      Turns the timer on/off. Will report the execution time of each command.

   !commandTimeout timeout_in_seconds

      Sets the command timeout. 0 sets the timeout to unlimited.

   !run commandfile [var1=val1,...]

      Run the given command file optionally passing in variable declarations.

   !consolewnd

      Shows the interactive console window.

   !history show [count]

      Shows all command history or the last 'count' commands.

   !history save filepath

      Saves the command history to a file for later playback (see the 'run' command).

   !history clear

      Clears the command history.

   !logopen filepath | !logclose

      Opens/closes log file. All input/output is written to the log.

Here’s an example of how to use the console to execute some commands, query for data and have timing information reported:

C:\>sseutil -c

Console mode. Type 'help' for more information.

> !timer

Timer enabled.

> !attach c:\northwind.mdf

Command completed successfully.

264 milliseconds.

> use "c:\northwind.mdf"

> go

Command completed successfully.

1 milliseconds.

> select customerid from customers where customerid like 'a%'

> go

customerid

-------------

ALFKI

ANATR

ANTON

AROUT

4 row(s) affected.

65 milliseconds.

Console window (-consolewnd command)

The console window offers a simple UI to edit and send commands to SQL as a batch. It also supports loading and saving script files. Using the console window makes it easier to iterate over a query and refine it than using the command line.

Using command files

SSEUtil supports the use of command files or files that contain SQL or Extended commands as available through the console mode. You can create new command files, load existing ones and execute them.

1. Creating a command file

Command files are simply text files where each line contains a command to run on the server. You can create new command files using SSEUtil or using any other editor like Notepad for example.

In SSEUtil, you can create a command file by going to the console (-c option) and typing ‘!history save filePath’. All commands currently in the command history will be saved to the command file. Below is an example:

C:\>sseutil -c

Console mode. Type 'help' for more information.

> !attach c:\northwind.mdf

Command completed successfully.

> use "c:\northwind.mdf"

> go

Command completed successfully.

> !timer

Timer enabled.

> SELECT CustomerID FROM Customers WHERE CustomerID LIKE 'a%'

> go

[...]

> !history save c:\mycommands.txt

Command file saved successfully.

      After this, all commands typed in the console will be saved in the mycommand.txt file including the extended commands like !attach and !timer.

2. Running a command file

You can run command files from the console (!run command) or by launching SSEUtil.exe with the –run command. All commands in the command file are executed one after the other. When running a command file from the console, you can omit the file path if a file was previously loaded. In that case it will just run the existing commands.

Here’s an example how to run the command file against the main instance from the command line:

C:\>sseutil -run c:\mycommand.txt -m

Command file loaded successfully.

> !attach c:\northwind.mdf

Command completed successfully.

> use "c:\northwind.mdf"

> go

Command completed successfully.

> !timer

Timer enabled.

> SELECT CustomerID FROM Customers WHERE CustomerID LIKE 'a%'

> go

customerid

-------------

ALFKI

[...]

4 row(s) affected.

74 milliseconds.

Command list playback completed.

Variable expansion

Command files can also contain variables. The variables will be replaced with the provided value(s) before the query is sent to the database server. Variables must be declared as follows in the file: _$$(VARNAME ). For example, if a file contains 'SELECT * FROM _$$(TABLENAME)', you can give it a value on the command like like this: 'sseutil –run script.txt tablename=Customers'. The SQL query sent will then be: 'SELECT * FROM Customers'.

--- END OF DOCUMENT ---