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
Advertisements