DOS File Management Commands for the SQL Server DBA

In this blogpost, I'll list out some useful DOS File Management Commands for the SQL Server DBA that you can leverage in administrative and research situations on your SQL Servers.

In DOS File Management Commands basically you'll learn

• Cut, Copy, Paste, Move, Delete, Rename of files

• Create & Remove directories

1. xcopy command (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.

Syntax :

xcopy c:\Test.txt d:\ /v /q /y /z

Example:

2. move command (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.

Syntax :

move /y c:Test.txt e:\

Example:

3. del command (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.

Syntax : Deleting Single File.

del e:\Test.txt

Example:

Syntax : Deleting Multiple Files.

del e:\1.txt e:\2.txt e:\3.txt

Example:

3. rename command (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.

Syntax :

rename d:\Test.txt Test1.txt

Example:

4. mkdir command (Create Directories)

Syntax:

mkdir d:\NewDir

Example1: Simple mkdir command example.

Example2: 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 

5. rmdir command (Remove Directories)

Syntax:

rmdir d:\NewDir

Example1: Simple rmdir command example.

Example2: 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

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .