Copy files from one directory to another using code

Question

Copy files from one directory to another using code

arrjay

Hall of Fame

Points: 3455
More actions
February 12, 2014 at 9:55 am

#282414

Hi there - I was wonder how you can copy all files with a certain file extension (.bak) from one location to another using sql code (not ssis).
I can copy a single file using something similar to the code below;
EXEC master..xp_cmdshell 'copy \\000.00.0.000\test.bak \\test-DB-Farm\test.bak'
However moving forward the file name is going to change but the .bak extension isn't.
Any help would be great!
--------------------------------------------
Laughing in the face of contention...

Viewing 8 posts - 1 through 7 (of 7 total)

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

Abu Dina SSChampion Points: 14155 More actions · Answer 1

arrjay (2/12/2014)
Hi there - I was wonder how you can copy all files with a certain file extension (.bak) from one location to another using sql code (not ssis).
I can copy a single file using something similar to the code below;
EXEC master..xp_cmdshell 'copy \\000.00.0.000\test.bak \\test-DB-Farm\test.bak'
However moving forward the file name is going to change but the .bak extension isn't.
Any help would be great!

If it's the DOS command you're after then it's something like this:

copy <Source path>\*.bak <Destination path>

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks for your response but I'm managing the whole process using sql therefore need the t-sql statement.

--------------------------------------------

Laughing in the face of contention...

Abu Dina SSChampion Points: 14155 More actions · Answer 3

arrjay (2/12/2014)
Thanks for your response but I'm managing the whole process using sql therefore need the t-sql statement.

Sure.. so just use it in your EXEC master..xp_cmdshell

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

arrjay Hall of Fame Points: 3455 More actions · Answer 4

Thats simple enough! Thanks!

--------------------------------------------

Laughing in the face of contention...

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

Personally I'd suggest you use Powershell and call that from xp_cmdshell. If you move this to an agent job or elsewhere, it's cleaner, and more flexible.

Here's a starter article: https://blogs.technet.com/b/heyscriptingguy/archive/2013/04/19/use-powershell-to-copy-files-and-folders-to-a-new-location.aspx

arrjay Hall of Fame Points: 3455 More actions · Answer 6

Thanks Steve. The process is managed by a sql job so a powershell approach would be good. I'll have a gander at what you sent over! Thanks again.

--------------------------------------------

Laughing in the face of contention...

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Steve Jones - SSC Editor (2/12/2014)
Personally I'd suggest you use Powershell and call that from xp_cmdshell.

BWAAA-HAAA!!!! Between the two of us, we're going to give Allen White a coronary!!!!:-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)