This article is a short summary of Windows utilities both GUI and command line intended for new SQL Server DBAs or those interested in learning some useful techniques. All examples use native Windows utilities/command or are available through Resource Kits, Administration Tools or Support Tools. Let's look at some examples.

FOR and FORFILES Commands

This simple command is used to delete old backups with .trn, sqb and bak extensions older than two days for files in the current directory and all subdirectories.

for %I in (TRN sqb bak) do FORFILES /S /D -2 /M *.%I /C "cmd /c del @file"

The for %I in (TRN sqb bak)

command executes Forfiles three times once for trn, sqb and bak. The forfiles command includes several switches:

• /S -- include all subdirectories
• /D -2 -- select files with date older than two days
• /M -- match .trn, sqb or bak extension

I've seen complicated T-SQL xp_cmdshells, Perl, VBScripts and bat files to do what turns out to be included in the OS! The FORFILES command is included in Windows 2003 and the Windows Resource Kit. It is not included in Windows XP,however you can simply copy the forfiles.exe file from any Windows 2003 Server and it will run on Windows XP also. The for command is included in Windows 2000, 2003 and XP.
Need to execute a SQL script against a bunch of SQL Servers, try this command:

for /f %i in (servers.txt) do SQLCMD -S%i -i C:\myscript.sql

Resource Kit Utilities

There are four Windows tools I'll install on every workstation I use:

• Windows Server 2003 Administration Tools Pack available as a download from Microsoft
• Windows Server 2003 Resource Kit Tools also available as a download from Microsoft
• Windows Server 2003 Support Tools which available from the Windows 2003 CD
• Windows Server 2000 Resource Kit Tools which unfortunately isn't available as a free download, however it can be downloaded from MSDN, if you have a current subscription

There are only four primary tools I'll regularly use as a DBA from the Windows 2003 Administration Tools Tools pack:

• Cluster Administrator for well, administering a cluster
• Terminal Services Manager for viewing who is using the Administrative Terminal Services connections and disconnecting someone who has forgotten to log off one of the connections
• Active Directory Users and Computers for viewing accounts, lockout status, group membership. Although I prefer to do most of this via the command line through other tools
• Computer Manager for connecting to remote systems to view the Event log, manage services and change local accounts

The Windows Server 2003 Resource Kit Tools and Windows Server Support Tools include many utilities, however there isn't one that I use on regular basis. The Windows Server 2000 Resource Kit Tools, however has three utilities which I find invaluable as a DBA:

• SHOWMBRS -- displays NT/AD group membership from the command line. Used primarily to assist in troubleshooting or assigning AD groups to SQL Server
• SHOWGRPS -- displays an NT/AD login groups from the command line. Used like SHOWMBRS only from the login perspective
• SRVINFO -- displays a wealth of information on the OS, hotfixes, drives, services, and uptime of remote systems from the command prompt

WMIC

WMIC is probably one of the most powerful, yet least used series of commands by administrators. It is included with Windows XP and Windows 2003 and can be used for shutting down services, monitoring process, or just getting information.

Need to shutdown the SQL Server services on all SQL Servers remotely? Try this single one-line command:

wmic /node:@C:\servers.txt service WHERE "Name Like 'MSSQL%' 
    And Name<> 'MSSQLServerADHelper'" CALL StopService

Need to know all of the SQL services running on a server?

wmic /node:MyServer service WHERE "(Name Like 'MSSQL%' OR Name Like 'SQLAgent%' 
   OR Name Like 'SQLServer%' OR Name = 'MSDTC') And Name<> 'MSSQLServerADHelper'" get DisplayName, Name, State

Here's one of my favorites, a disk command that displays output similar the UNIX df -kH command, it's a little more complex than the previous examples in that it uses a custom XSL transform. One of the interesting thing about WMIC is that it can output XML which in turn can be transformed using XSL, so I created an XSL file called logicaldisk.xsl. Copy the xsl file to the C:\Windows\System32\Wbem directory to use this command.

wmic /node:MyServer logicaldisk get Caption, VolumeName, FreeSpace, Size, SystemName /format:logicaldisk.xsl

And the output of the command will look something like this:

Caption       FreeSpace     Size          SystemName    VolumeName    Free%
A:                                        MYSERVER                     
C:            0.8G          8.4G          MYSERVER                     10%
E:                                        MYSERVER                     
F:            6.6G          11.2G         MYSERVER       EMC_12        59%
I:            57.8G         67.4G         MYSERVER       EMC_67        86%
J:            62.0G         67.4G         MYSERVER       EMC_67        92%

Windows Command-Line Scripts

Yes, Windows command-line script aka bat files are still relevant in today's world of VBScript and WMI, a few lines in a bat file can be quickest way to write a useful tool. And WMIC is a great way to do a lot of things quickly, however remembering all of the commands and having to type a long command string doesn't seem too appealing. Here's where command line scripts really shine, abstracting away a complex command to simple one word command accepting parameters. For example the command listing disk information can be wrapped in a command line script as follows:

@echo off
@if "%1"=="?" goto Syntax
@if "%1"=="" goto Local
rem **********************************
rem Script disk.bat
rem Creation Date: 5/14/2006
rem Last Modified: 5/14/2006
rem Author: Chad Miller cmille19@tampabay.rr.com
rem ***********************************
rem Description: Display logical disk information
rem NOTE: COPY logicaldisk.xsl to C:\Windows\System32\Wbem to USE!!!
rem ***********************************
:Loop
if {%1}=={} goto :EXIT
@echo ************************
@echo *** Machine: %1
@echo ************************
wmic /node:%1 logicaldisk get Caption, VolumeName, FreeSpace, Size, SystemName /format:logicaldisk.xsl 2>&1
SHIFT /1
goto Loop
:Local
@echo ************************
@echo *** Machine: %COMPUTERNAME%
@echo ************************
wmic logicaldisk get Caption, VolumeName, FreeSpace, Size, SystemName /format:logicaldisk.xsl 2>&1
goto :EXIT
:Syntax
@echo Syntax: disk [machine1 machine2 machine3 ...]
goto :EXIT
:EXIT

We're still basically executing the same one-line command as before, we've just added some icing to our original solution, so that we can enter a single command disk to display disk information. By the default the local machine disk information is displayed. To display disk information for a remote server enter disk MyServer1 or to display disk information from multiple machines by entering each server separated by a space, disk MyServer1 MyServer2

Conclusion

There are many more Windows utilities and commands than can be covered here, this article has demonstrated only a few commands and Windows utilities which are relevant to a SQL Server DBA. The CLIforDBA.zip file contains several examples of Windows script files.