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.