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

reconfigure

go

--by default OLE Automation Procedures is off for security concern

--we can enable it by following command

sp_configure 'Ole Automation Procedures',1

reconfigure

go

DECLARE @FsObjId        INTEGER

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

GO



Happy Coding!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com