Copy files with SQL Server from one location to another location.

Well when talk comes to file handling, SQL guys says, hey this is not DBA job why don’t you keep it for .NET programmers!!! It is not like that, DBA/SQL Developer must have to be aware with file handling technique in SQL Server they might need to take backup regularly so old backup should be deleted or transferred somewhere else and this could be done by file handling only ;) isn’t it DBA job? J

Anyway, let us come to the points, there are quite a few methods to this task, I will introduce one of them today. Before we move towards our script I would like you to create one folder as source folder with few files in it and create one folder as destination. I have created “Ritesh” folder in “C” drive with few .TXT files in it and created “Ritesh” folder in “D” drive and there is not file at all in “D:\Ritesh”

Once you create your folder structure, let us see the script.

–show advanced configuration on
sp_configure ‘show advanced options’,1

–by default OLE Automation Procedures is off for security concern
–we can enable it by following command
sp_configure ‘Ole Automation Procedures’,1

DECLARE @Source         VARCHAR(4096)
DECLARE @Destination    VARCHAR(4096)
SET @Source = ‘C:\ritesh’
SET @Destination= ‘D:\ritesh’
–creare OLE Automation instance
EXEC sp_OACreate ‘Scripting.FileSystemObject’, @FsObjId OUTPUT
–call method of OLE Automation
EXEC sp_OAMethod @FsObjId, ‘CopyFolder’, NULL, @Source, @Destination
–once you finish copy, destroy object
EXEC sp_OADestroy @FsObjId

Happy Coding!!!

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
Microsoft SQL Server Blog. Fight the fear of SQL with Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.