Learn more about SQL Server tools

   
   















































DOS Commands for the SQL Server DBA

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (8)   |   Related Tips: More > DBA Best Practices

Problem
I have seen your recent tips (Introduction to Windows PowerShell for the SQL Server DBA Part 1, Introduction to Windows PowerShell for the SQL Server DBA Part 2 and PowerShell Support in SQL Server 2008 - Invoke-Sqlcmd Cmdlet) on PowerShell and see value in them.  Unfortunately, we do not have PowerShell installed on our SQL Servers and I am not sure when that is going to happen.  Until that day arrives could you give me some insight into valuable DOS commands that I can leverage in administrative and research situations on my SQL Servers?

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. 

In an earlier tip (Accessing the Windows File System from SQL Server) we outlined some options to access files and directories, so be sure to check that out.  To build on those commands let's see how to do the following:

  • File management
    • Copy, cut, paste, move, delete, etc. files
    • Create and remove directories
  • Troubleshooting and Research
    • Gather system information - Winmsd
    • 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


File Management

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.
-- 1 - Declare variables
DECLARE @CMD1 varchar(8000)
DECLARE @RestoreRootDirectory varchar(255)
DECLARE @CurrentDate datetime
DECLARE
@CurrentName varchar(8
)

-- 2 - Initialize variables
SET @CMD1 = ''
SET @RestoreRootDirectory = 'C:\Temp\'
SET @CurrentDate = GETDATE()
SELECT @CurrentName = CONVERT(varchar(8), @CurrentDate, 112
)

-- 3a - Create the current directory
SELECT @CMD1 = 'EXEC master.dbo.xp_cmdshell ' + char(39) + 'mkdir ' + @RestoreRootDirectory + @CurrentName + '\' + char(39)
-- SELECT @CMD1
EXEC(@CMD1)

-- 3b - Test the error value
IF @@ERROR <> 0
BEGIN
RAISERROR
('3a - Restore directory not created', 16, 1)
RETURN
END
 

Remove Directories - In the example code below, we are removing a directory based on the the current date minus one.
-- 1 - Declare variables
DECLARE @CMD1 varchar(8000)
DECLARE @RestoreRootDirectory varchar(255)
DECLARE @CurrentDate datetime
DECLARE
@PreviousName varchar(8
)

-- 2 - Initialize variables
SET @CMD1 = ''
SET @RestoreRootDirectory = 'C:\Temp\'
SET @CurrentDate = GETDATE()
SELECT @PreviousName = CONVERT(varchar(8), @CurrentDate-1, 112
)

-- 3a - Drop the previous directory
SELECT @CMD1 = 'EXEC master.dbo.xp_cmdshell ' + char(39) + 'rmdir ' + @RestoreRootDirectory + @PreviousName + '\ /q' + char(39)
-- SELECT @CMD1
EXEC(@CMD1
)

-- 3b - Test the error value
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

 

Next Steps



Last Update: 4/14/2009


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, December 06, 2014 - 1:24:53 AM - Dinesh DBA Read The Tip
TRACERT

A tracert is similar to ping, but will show the details the connection takes to the server. This is a useful debugging tool for generic server access problems.

CMd:- Tracert google.com

Saturday, December 06, 2014 - 1:20:17 AM - Dinesh DBA Read The Tip

 Telnet is a user command and an underlying TCP/IP protocol for accessing remote computers.What is Telnet?

Telnet is a user command and an underlying TCP/IP protocol for accessing remote computers. Through Telnet, an administrator or another user canaccess someone else's computer remotely.

Example:- 1) Telnet 127.0.0.1 1433 :- which is useful for mssql connectivity port.

                  2) Telnet 192.168.0.12 1433 :-  reomot computer port connection.


Saturday, December 06, 2014 - 1:10:49 AM - Dinesh DBA Read The Tip

Start/Stop SQL Server from command line

You can start/stop SQL Server from command line using NET command as below:

NET Action Service Name or Service Display Name.

example:-

To START SQL Server Service, use:

NET start MSSQLSERVER

OR

NET start "SQL Server (MSSQLSERVER)"

 

 

To STOP SQL Server Service, use:

NET stop MSSQLSERVER

OR

NET stop "SQL Server (MSSQLSERVER)"

 


Saturday, December 06, 2014 - 1:05:53 AM - Dinesh DBA Read The Tip

sqlcmd Utility:-

 sqlcmd -S myServer\instanceName. Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.

Ex:- Sqlcmd -S . -E    => will connect Local Machine with Window authetication

       Sqlcmd -S  FinaceServer -E => will connect  FinaceServer sql server with Window authetication.

      Sqlcmd - => will show network sql server installled.

     


Saturday, December 06, 2014 - 12:42:38 AM - Dinesh DBA Read The Tip

Path:-

Sets the command path in the PATH environment variable (the set of directories used to search for executable files). If used without parameters, path displays the current command path.


Saturday, December 06, 2014 - 12:23:38 AM - Dinesh DBA Read The Tip

@HARI,

 Advanced information about users Using DOS Command:- (NLTEST /user:"Username")

The following example shows detailed information about a specific user.

nltest /user:"TestAdmin"

 

This command displays output similar to the following:

User: User1
Rid: 0x3eb
Version: 0x10002
LastLogon: 2ee61c9a 01c0e947 = 5/30/2001 13:29:10
PasswordLastSet: 9dad5428 01c0e577 = 5/25/2001 17:05:47
AccountExpires: ffffffff 7fffffff = 9/13/30828 19:48:05
PrimaryGroupId: 0x201
UserAccountControl: 0x210
CountryCode: 0x0
CodePage: 0x0
BadPasswordCount: 0x0
LogonCount: 0x33
AdminCount: 0x1
SecurityDescriptor: 80140001 0000009c 000000ac 00000014 00000044 00300002 000000
02 0014c002 01050045 00000101 01000000 00000000 0014c002 000f07ff 00000101 05000
000 00000007 00580012 00000003 00240000 00020044 00000501 05000000 00000015 22cd
b7b4 7112b3f1 2b3be507 000003eb 00180000 000f07ff 00000201 05000000 00000020 000
00220 00140000 0002035b 00000101 01000000 00000000 00000201 05000000 00000020 00
000220 00000201 05000000 00000020 00000220
AccountName: User1
Groups: 00000201 00000007
LmOwfPassword: fb890c9c 5c7e7e09 ee58593b d959c681
NtOwfPassword: d82759cc 81a342ac df600c37 4e58a478
NtPasswordHistory: 00011001
LmPasswordHistory: 00010011
The command completed successfully
--------------

Saturday, December 06, 2014 - 12:20:04 AM - Dinesh DBA Read The Tip

Verify trust relationship with a specific server Using Dos Commad--NLTEST

The following example verifies that the a-dc1 server has a valid trust relationship with the domain.

nltest.exe /server:fourthcoffee-dc-01 /sc_query:fourthcoffee

This command displays output similar to the following:

Flags: 30 HAS_IP  HAS_TIMESERV
Trusted DC Name \\fourthcoffee-dc-01.forthcoffee.com
Trusted DC Connection Status Status = 0 0x0 NERR_Success
The command completed successfully
------------

Monday, June 16, 2014 - 11:14:01 AM - Hari Read The Tip

How do i check the sql account's status in dos command?

 

thanks,

Hari




 
Sponsor Information