Copy or delete files from directory in SQL Server

How to copy or delete files from directory in SQL Server ?

It can be done using xp_cmdshell which is SQL Server built in system stored procedure is used to commands directly in the windows command prompt.

To use system stored procedure xp_cmdshell, you need to enable it in the system configuration using the sp_configure.

Following are the steps to enabling it, First enable advance option in master database.

 

USE master; 
GO

-- To allow advanced options to be changed.
EXECUTE sp_configure 'show advanced options', 1; 
GO 
-- To update the currently configured value for advanced options. 
RECONFIGURE; 
GO

 

 

Now, Enable Xp_cmdshell extended stored procedure.

 

-- To enable the feature. 
EXEC sp_configure 'xp_cmdshell', 1; 
GO 
-- To update the currently configured value for this feature. 
RECONFIGURE;

 

 

Now we have enabled xp_cmdshell successfully, Lets use the feature of xp_cmdshell for copying and deleting the files from window directory.

 

Copy files using xp_cmdshell

Here we have two folder in directory, one is SourceData and Second is TargetData folder, as you can see in below screenshot. SourceData folder contains three files while TargetData folder is empty.

 

 

 

Now our task is, to copy all files from SourceData folder to TargetData folder.

SourceData folder path is E:\SourceData, and TargetData folder path is E:\TargetData, same can be seen in above screenshot.

 

Lets use copy command in xp_cmdshell for copying files from SourceData folder to TargetData folder.

 

EXEC xp_cmdshell 
'copy E:\SourceData E:\TargetData';

 

After executing the Statement, It returns the names of all copied files and counts.

 

 

Lets check the TargetData folder to ensure that whether the files are copied or not, and you can see now these files are copied to TargetData folder successfully.

 

Delete files using xp_cmdshell

You can use DEL command in the XP_Cmdshell to delete the file from the specified directory.

Here we have a file named as studentInfo.txt in TargetData folder ( Path – E:\TargetData) as shown in below screenshot, Lets delete this file.

 

Following statement uses DEL command to delete the files StudentInfo.txt.

 

EXEC xp_cmdshell 
'del E:\TargetData\StudentInfo.txt'

 

As you can see, it executed successfully with 1 row affected.

 

 

Lets check the TargetData folder to ensure that files is being deleted or not.

Get the lists of files from directory to SQL table

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 17,880 total views,  5 views today