In my latest article, I'm going to briefly touch on thirty useful administrative command line utilities for Microsoft SQL Server 7.0. The tools described below primarily serve for diagnostics, configuring and migration, rather than for database server
administration. Some of these tools (for example Bcp, Distrib
and Osql) have complex syntax (set of command line parameters), meaning you'll likely need permanent access to Microsoft SQL Server 7.0 electronic documentation - Books Online, which is available with your database server compact disc.
Bcp - A utility intended for bulk copying of SQL Server data
(can be any table, view or Transact-SQL result set) and/or data files
(in a user-specified format) or any other database management systems.
Bcp is frequently used to transfer large volumes of data among
Microsoft SQL Server databases and other RDBMS databases.
Cnfgsrv - An undocumented utility -- try to run it with the /?
option to receive a set of acceptable command line parameters.
Microsoft SQL Server probably uses cnfgsrv.exe when the setup attempts to configure
the SQL Server computer.
Console - An operating system command prompt utility that displays informational
messages during backing up SQL Server databases or when restoring databases from tape dump devices.
Dcomscm - A COM-component used for SQL Server program management
and its services state checking.
Distrib - A Replication Distribution Agent command prompt utility
that configures and begins a replication Distribution Agent, which moves
transactions and snapshot jobs held in the distribution database tables
Dtsrun - A utility for retrieving, executing, deleting and overwriting
packages, previously created using Microsoft Data Transformation Services.
The DTS package can be stored in the Msdb database, a COM-structured
storage file or Microsoft Repository.
Dtswiz - A utility for running Microsoft Data Transformation Services
import and export wizards from a command prompt and creating DTS packages
that import, export or transform data among different ODBC-data sources.
Isql - A utility for interactive Transact-SQL statements, system
procedures and SQL Server scripts execution. Isql uses DB-libraries
for database server connections started directly from the operating system
command prompt and uses standard output devices (monitor, by default) for
Kill6x - A utility for removing «old», 6.x versions
of Microsoft database servers.
Langinst - A utility for installing additional languages support
through the SQL Setup application.
Logread - A utility used for configuring and starting a replication Log
Reader Agent. All transactions (marked for replication) move from the
transaction log on the Publisher to the distribution database.
Makepipe - A diagnostic utility used for network Named Pipe services
integrity testing (checks if working). The Makepipe utility should
be launched from the database server console, and it expects connection from Readpipe, which
should be started at the problem SQL-client console. After Makepipe is started,
the database server waits for a client to connect. In other words, Makepipe
is used in conjunction with Readpipe utility (see below).
Odbccmpt - A migration assistance utility that enables or disables
the Microsoft SQL Server 6.5 ODBC compatibility option for an ODBC application
executable file. Odbccmpt should be used ONLY (!!!) for applications
having problems when moved to SQL Server 7.0.
Odbcping - A diagnostic utility every DBA should use for testing ODBC data sources integrity
(in case of ODBC-related connection problems) and for establishing successful connections between the SQL client and database server.
Osql - A utility for interactive Transact-SQL statements, system
procedures and SQL Server scripts execution. Osql uses ODBC libraries
for database server connection and is started directly from the operating system
command prompt. Osql uses standard output devices (monitor, by default) for
Readpipe - A diagnostic utility used for network Named Pipe services
integrity testing (checks if working). Readpipe is launched on a SQL client
console that is unable to connect to SQL Server. Readpipe is used in conjunction
with Makepipe utility (see above).
Rebuildm - A utility used for (1) fixing a corrupt master database, and
(2) changing the character set, sort order or Unicode collation for a Microsoft
SQL Server installation. Be sure you have full dumps for all user databases
or all de-attached data and log files for all user databases. After rebuilding
the master database you need to re-create the database objects and reload
the data, or attach the data and log files for all databases you need
using the sp_attachdb system stored procedure.
Regrebld - A utility database server administrators use to backup
and restore Microsoft SQL Server 7.0 entries in the system registry if they
Replmerg - A Replication Merge Agent utility that configures and
starts a replication Merge Agent and then applies initial snapshots held
in the database tables of the Publisher to the Subscribers. Replmerg
also merges data changes made by the Publisher or Subscribers after the
initial snapshot is created.