Solution PowerShell offers a great deal of value, but if it is not installed on your SQL Servers, then working through that process in some large organizations could take time. I would recommend moving down that path, but until that day comes, there are some DOS commands that can be valuable as well.
Validating a machine is online after a reboot - Ping
Active Ports - netstat
Network cards - ipconfig
Free disk space - master..xp_fixeddrives
Directory structure - master..xp_subdirs
Before we go too far down this path, using these commands in SQL Server is based on having the extended stored procedure master..xp_cmdshell enabled. In SQL Server 2000, in general this was accomplished by having SQL Server System Administrator rights. In SQL Server 2005, the master..xp_cmdshell extended stored procedure is enabled by using the Surface Area Configuration manager. In SQL Server 2008, enabling the master..xp_cmdshell extended stored procedure is accomplished by properly configuring the correct facet.
One set of logic I have used time and time again in T-SQL administrative scripts is related to file management. Whether it is from an automated maintenance task or backup\recovery\log shipping, managing files has been a necessity.
DOS File Management Commands
Copy and Paste Files - The xcopy command is handy when you need to copy and paste files from one directory to another. In the example below we are copying the Test.txt file in the C:\temp directory to the root of the C:\ drive. The /v switch verifies the file as it is written to the destination directory, the /q suppresses the display of messages, /y switch indicates that suppresses a prompt to overwrite an existing file and the /z switch copies the file in a mode where the file could be restarted. This command has additional options available than the copy command related to including subdirectories, archived files, verifying files, etc.
Cut and Paste Files - When it comes to cutting and pasting files, I prefer to use the move command. It is a simple command with a single switch to suppress any prompting followed by the source file and destination directory. Another alternative is to use the xcopy command listed above and then one of the delete commands listed below for more advanced deleting techniques.
Delete Files - Deleting files is imperative to ensure disk drives to not fill up. Although disk is cheap at some point it gets expensive to manage (people) and power the SAN\NAS\DASD devices.
Here are a few different tips that have already been written on the topic:
Rename Files - Since we are talking about files, in many of the scripts I have written I have renamed files so it is easy to determine that they have been processed. At the most simple level, the rename command can be called with the current directory and file name followed by the new file name.
Create Directories - In the example code below, we are creating a new directory based on the current date with the mkdir DOS command.
IF @@ERROR <> 0 BEGIN RAISERROR ('3a - Restore directory not deleted', 16, 1) RETURN END
Troubleshooting and Research
DOS Troubleshooting and Research Commands
Gather system information - Winmsd can be invoked directly from the Start | Run command by typing in WINMSD. With this command you are able to get a basic set of information about the machine.
Validating a machine is online after a reboot - Although the ping command will not tell you when your application is operational after a reboot, it will let you know when Windows should be operational so you can begin to validate SQL Server has recovered and the application is operational.
Active Ports - The netstat -a command is valuable to provide the active TCP connections with the TCP and UDP ports the SQL Server is listening on.
Network Cards - Another command for general troubleshooting is the ipconfig command. In this example we are listing all of the information across each of the NICs, but this command does offer more options in terms of flushing DNS, releasing DNS, renewing DNS, etc.
Free disk space - The master..xp_fixeddrives extended stored procedure lists the free space in MB per disk drive. One situation I have run into with processes requiring a significant amount of disk space on a monthly basis is to check the free disk space prior to running the remainder of the code. It is frustrating to have a process run for an extended period of time only to find out sufficient disk space was not available in the first place. Check out the Determine Free Disk Space in SQL Server with T-SQL Code tip for additional information.
EXEC master.sys.xp_fixeddrives GO
Directory structure - The master..xp_subdirs extended stored procedure provides the ability to capture the sub directories.
EXEC master.sys.xp_subdirs 'c:\' GO
As you are faced with more situations where you need to access the Windows file system, be sure to see what options are available with extended stored procedures, DOS and PowerShell.
As your organization has time, be sure to check out PowerShell to see how this new product can improve your overall infrastructure management.
For more information about master..xp_cmdshell visit: