SQL Server - File Operations Using xp_cmdshell (List, Read, Write, Copy, Delete, and Move Files)

Views: 4.784
Reading Time: 6 minutes

Hello everybody!

In this post, I will demonstrate how to perform file operations using the xp_cmdshell procedure, how to copy files, delete, rename, move, create directories, etc.

This post is a complement to other file operations using other solutions:

To enable xp_cmdshell, simply run the command below:

stpArchive_Exists
Stored Procedure that returns a Boolean variable (BIT) informing if a given file or directory exists.

Source code

Usage examples

Checking if a directory exists

Checking if a file exists

stpArchive_Listar
Stored Procedure that returns the listing of files and subdirectories of a given directory.

Source code

Result:

stpArchive_Ler
Stored Procedure that reads a text file and returns its contents in table form.

Source code

Result:

As you may have noticed, importing accented files doesn't work very well, because the DOS Prompt type command, even under Windows in Portuguese, doesn't work that well. Alternatively, we can use the old known Power Shell.

Source code in Powershell

Result:

stpWrite_File
Stored Procedure that writes text to a file. Due to a limitation of DOS Prompt, it is not possible to write text with line breaks (for this, use the solution with OLE Automation or CLR).

Source code

Result:

stpApaga_Archive
Stored Procedure that physically deletes a file on disk or network.

Source code

Result deleting a file:

Result using wildcard:

stpCopia_File
Stored Procedure that copies a file to another directory, either locally or on the network.

Source code

Result:

stpMove_File
Stored Procedure that moves a file to another directory, either local or network.

Source code

Result:

stpCria_Directory
Stored Procedure that creates a directory in a specified location.

Source code

Result:

stpApaga_Directory
Stored Procedure that deletes a directory, with the possibility of deleting files and subfolders as well.

Source code

Result:

As I commented in some blog posts here, I'm not a fan of enabling and using xp_cmdshell, since it gives "powers" to any instance sysadmin user to execute any command that the user starting the SQL Server service has privilege , from a dir command, to delete, format, etc.

For this reason, I prefer to use CLR, where you program the routines using the C # programming language and the .NET Framework and publish the routines in the instance. Both sysadmin and normal users are only able to use the methods implemented in the CLR library, not allowing unscheduled malicious code to run, but I understand that not every DBA wants to do the work of creating C # libraries and ends up choosing to use a simpler approach using xp_cmdshell and DOS and / or PowerShell commands.

That's it folks!
I hope you enjoyed this post and see you next time.