Home ยป Copy or delete files from directory in SQL Server

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 3,580 total views,  21 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.