…try SQL Backup pro for faster, smaller, more robust backups.

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: Maintenance task to delete old SQL Server backup files CLR function to delete older backup and log files in SQL Server Using the FORFILES Command to Delete SQL Server Backups Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files

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

Next Steps

• 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:
  • Enabling xp_cmdshell in SQL Server 2005
  • Where is the Surface Area Configuration tool in SQL Server 2008
• For more information about PowerSheel visit:
  • Introduction to Windows PowerShell for the SQL Server DBA Part 1
  • Introduction to Windows PowerShell for the SQL Server DBA Part 2
  • PowerShell Support in SQL Server 2008 - Invoke-Sqlcmd Cmdlet

• Using the Microsoft SQL Server 2005 Best Practices Analyzer
• Best practices for working with read only databases in SQL Server
• Best Practices – Backup System Databases in SQL Server
• SQL Server 2005 Index Best Practices
• SQL Server Integration Services (SSIS) - Best Practices
• More...

• Building a Data Mart with Integration Services

• Discuss this tip, post comments and feedback