How to move / Archive filestream Files from one directory to another directory using SQL SCRIPT?

Question

How to move / Archive filestream Files from one directory to another directory using SQL SCRIPT?

manavairajan

Right there with Babe

Points: 781
More actions
September 4, 2017 at 4:14 am

#339683

Hi Every one.
I need a help!
I am new to this File table's files stream concept and I have a requirement like everyday archive the files stream data from a directory to another directory.
How to move files a file directory to another directory programmatically (Through SQL sript).
Example :
Source files path: D:\DATA\currentdatefolder\
files : abc.txt
efg.img
hij.Png
lkm.xlx
Destination path : D:\DATA\Archive\currentdatefolder\
files : abc.txt
efg.img
hij.Png
lkm.xlx
The above mentioned files how to move source to destination.
Need your help for achieve this.
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

A filetable isn't a folder like d:\data\currentdatefolder. It's a share on the system that is managed by SQL Server. The idea with Filetable/Filestream is that you can access the data outside of sQL Server as files, or inside as blobs and things stay in sync.

There are two choices here. First, use normal Windows copy/move functions to move the files. You can access \\myserver\myfiletable with xcopy or other copy utilities and move the files to another folder. This is useful if you don't need the files accessed from inside SQL Server.

The second choice is setting a second Filetable share for the new folder location you want, and then using T-SQL to move the rows from one table to the next. That will get you an archive. Note, if you use a second Filetable, you will still be including the files in a backup.

If you explain in more detail the architecture or how you use the files/data, perhaps there is a recommendation one way or the other.

manavairajan Right there with Babe Points: 781 More actions · Answer 2

Thank you so much for your quick response, But,I took delay to view your response.

The files stream seems to occupying spaces in two places like physical files as well as file stream binary data. So, currently Holt on this investigation.
I have to study more on this once i complete below activities.

Now, I trying to convert Blob data in image using sp_OACreate and sp_OAMethod. But, which not allowing save file in remote location. Could you please help on this
I given path like this.
'\\<SERVERName>\d$\Storage\Activity_2017'

Thanks in Advance.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

You must use the share path defined in Config Manager. You CANNOT use a physical path.